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.

Depending on the subquery, it is possible MySQL thinks it is a "correlated" subquery, and is running it repeatedly, once for every row in wp_posts. I sometimes find INNER JOINing to such subqueries faster; and failing that, just putting the subquery results into a TEMPORARY table, and joining against that instead. — Uueerdo
– Uueerdo, Commented Jul 7, 2015 at 19:12

Darren H · Accepted Answer · 2015-07-07 19:33:34Z

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

answered Jul 7, 2015 at 19:33

Darren H

9018 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Epsiloncool Over a year ago

Thanks. I think it will help, however I can't use left join in main query, this is a restriction of my task. I can put subquery only to "WHERE" block of main subquery. I've already tried to put one more "from" above subquery, but it was not helpful.

Epsiloncool Over a year ago

I have one "like" operator in subquery thats why it runs without index, I see it in EXPLAIN, however I can't remove this operator and I think I can't put an index to it. As I said before, I have subquery running at 0.15 sec which is good for me. I am waiting ~0.2 sec from whole query, this is a goal which I need to get to.

Darren H Over a year ago

That is an issue with the structure. If the IDs are not indexed then it will run the subquery once for every row in wp_posts. So if your subquery runs at 0.15s but there are 100 records in wp_posts then the total will be 15s. If there are 10000 records then this becomes a big problem. Either ensure the IDs are indexed in both tables (link them as a foreign key if you can) or you'll need to abandon the idea of using a subquery in this way.

Epsiloncool Over a year ago

Thank you, which IDs you mean? In source tables all IDs are indexed of course. Only not indexed place is a "LIKE '%..%'" operator in subquery (which is not about ID, but used to search some substring in other table column).

Darren H Over a year ago

LIKE %..% is notoriously slow. That would be why it's taking 0.15s instead of 0.00015s. If you can get around using that then even when the subquery is loaded thousands of times it will still be acceptably quick. Can we see the subquery in order that I or someone else may find a way around it?

|

Drew · Accepted Answer · 2015-07-07 19:20:15Z

1

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/

answered Jul 7, 2015 at 19:20

Drew

25k10 gold badges47 silver badges81 bronze badges

Comments

Epsiloncool · Accepted Answer · 2015-07-09 11:56:52Z

0

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.

answered Jul 9, 2015 at 11:56

Epsiloncool

1,4851 gold badge20 silver badges41 bronze badges

Collectives™ on Stack Overflow

Mysql long subquery "in" issue

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related