Mysql long subquery "in" issue

Question

I can't understand an issue in simple MySQL query. Look here, pls

select SQL_NO_CACHE
    ID,
    post_title,
    post_type
from wp_posts 
where 1
  and (wp_posts.ID in (select id from ...[complex subquery here]...))

This query running quite long (2.5 sec), but if I running subquery separately (that one which goes from "in (..") it takes 0.15 sec only. The subquery returning only 60 rows and I think whole query HAVE TO run faster in this case.

Btw I've tried to run whole query with fixed ID list instead of subquery, like

select SQL_NO_CACHE
    ID,
    post_title,
    post_type
from wp_posts 
where 1
and (wp_posts.ID in
(
48393,
52796, .... 58 more numbers))

and it is working very fast (~1 ms).

Where is an issue? Why whole query running so slow and how I can improve this? Thanks.

Answer 1

As mentioned above, mysql is not great at optimizing queries in this kind of situation. What is likely happening is it's doing the subquery once for every record in wp_posts. Avoid this behavior by combining them into a single query with a join

select SQL_NO_CACHE
    ID,
    post_title,
    post_type
from wp_posts
left join another_table on wp_posts.id = another_table.post_id
where {complex conditions from your other query}

Hope this is helpful