2 very fast and simple queries are terribly slow when merged together

Question

I have these 2 tables. I'm trying to look for the groups the user with id 1 has joined. Here are 10 rows of each table (just to show their layout):

PostTable (57,272 rows, primary key id):

+----+---------+
| id | groupid |
+----+---------+
|  0 |       1 |
|  1 |       1 |
|  3 |       1 |
|  4 |       1 |
|  5 |       1 |
|  9 |       1 |
| 10 |       1 |
| 13 |       1 |
| 15 |       1 |
| 17 |       1 |
+----+---------+

JoinedGroupsTable (258,404 rows, unique index userid,groupid):

+--------+---------+--------+
| id     | groupid | userid |
+--------+---------+--------+
| 258010 |       1 |      1 |
| 258484 |       6 |      1 |
|    172 |       1 |      2 |
|    173 |       2 |      2 |
|    174 |       3 |      2 |
|    175 |       4 |      2 |
|    176 |       5 |      2 |
|    177 |       6 |      2 |
|    178 |       8 |      2 |
|    179 |       9 |      2 |
+--------+---------+--------+

When I try to run this query, it finished in almost 3 seconds which is really slow:

SELECT * FROM posttable p 
WHERE groupid in (SELECT groupid FROM joinedgroupstable WHERE userid=1)
ORDER BY p.ID DESC LIMIt 25;

I've also tried to use INNER JOIN rather than WHERE IN, but ended up with roughly the same result:

SELECT * FROM posttable p 
INNER JOIN joinedgroupstable jg ON userid=1 AND jg.groupid=p.groupid
ORDER BY p.ID DESC LIMIt 25;

Here is the EXPLAIN SELECT for both queries (Same result for both queries):

|| *id* || *select_type* || *table* || *partitions* || *type* || *possible_keys* || *key* || *key_len* || *ref* || *rows* || *filtered* || *Extra* ||
|| 1 || SIMPLE || jg ||  || ref || UserID_GroupID,userid || UserID_GroupID || 4 || const || 2 || 100.00 || Using index; Using temporary; Using filesort ||
|| 1 || SIMPLE || p ||  || ref || groupid || groupid || 4 || thyra.jg.groupid || 60 || 100.00 ||  ||

The thing is that running each query on its own is super fast:

SELECT * FROM posttable p ORDER BY p.ID DESC LIMIt 25;

SELECT * FROM joinedgroupstable WHERE userid=1

What could be wrong considering each query runs super fast on its own but slow when merged?

Answer 1

I would use EXISTS instead that could be also perform better :

select p.*
from posttable p 
where exists (select 1 
              from joinedgroupstable jg
              where jg.groupid = p.groupid and jg.userid = 1
              )
order by p.id desc
limit 25;