CODEWITHSUNDEEP
mysqloptimizationquery-optimization
Epsiloncool
Epsiloncool

Reputation: 1473

Mysql long subquery "in" issue

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.

Upvotes: 0

Views: 58

Answers (3)

Epsiloncool
Epsiloncool

Reputation: 1473

Thanks for all, I've rebuilt a query and used "inner join" way. It looks like there is no good way to fix it with an idea I've planned initially.

Upvotes: 0

Darren H
Darren H

Reputation: 902

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

Upvotes: 1

Drew
Drew

Reputation: 24959

Your numbers are effectively an un-indexed table. Run your query thru explain and witness the tablescan woe.

The following can explain an execution plan:

http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/

Upvotes: 1

Related Questions