37

I have a MySQL query (Ubu 10.04,Innodb, Core i7, 16Gb RAM, SSD drives, MySQL params optimized):

SELECT
COUNT(DISTINCT subscriberid)
FROM
em_link_data
WHERE
linkid in (SELECT l.id FROM em_link l WHERE l.campaignid = '2900' AND l.link != 'open')

The table em_link_data has about 7million rows, em_link has a few thousand. This query will take about 18 seconds to complete. However, if I substitute the results of the subquery and do this:

SELECT
COUNT(DISTINCT subscriberid)
FROM
em_link_data
WHERE
linkid in (24899,24900,24901,24902);

then the query will run in less than 1 millisecond. The subquery alone runs in less than 1ms, the column linkid is indexed.

If I rewrite the query as a join, also less than 1ms. Why is a "IN" query so slow with a subquery in it and why so fast with values in it? I can't rewrite the query (bought software) so I was hoping there is some tweak or hint to speedup this query! Any help is appreciated.

Improve this question
4
  • What does your explain plan say? What indexes do you configured? Commented Feb 16, 2011 at 15:25
  • 2
    My guess is that em_link needs an index containing campaignid and link. Commented Feb 16, 2011 at 15:25
  • This is the optimizer results: select count(distinct ackci.em_link_data.subscriberid) AS COUNT(DISTINCT subscriberid) from ackci.em_link_data where <in_optimizer>(ackci.em_link_data.linkid, <exists>(<primary_index_lookup>(<cache>(ackci.em_link_data.linkid) in em_link on PRIMARY where ((ackci.l.campaignid = '2900') and (ackci.l.link <> 'open') and (<cache>(ackci.em_link_data.linkid) = ackci.l.id))))) Commented Feb 16, 2011 at 15:38
  • I believe MySQL 5.6.7 fixes this problem with the materialization option. If the sub-query is independent of the outer query, then it gets executed once, turned into temporary table internally, then joined to the outer query. This has always been a very frustrating problem with MySQL, something that Oracle managed to get right several decades ago. Commented Nov 14, 2013 at 11:04

4 Answers 4

Reset to default
32

Subqueries execute every time you evaluate them (in MySQL anyway, not all RDBMSes), i.e. you're basically running 7 million queries! Using a JOIN, if possible, will reduce this to 1. Even if adding indexing improves performance of those, you're still running them.

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

7 Comments

But how come the 2nd example is extra-fast where I provide the results of the subquery? Won't mySQL execute the subqueries 1st then the main query? I'm thinking the optimizer is dropping the ball on something... If it executes the subquery 1st and gets a list of 4 values then executes the main query it should be fine... I have indexes in all the columns suggested...
@Franco - Yep the performance of the optimizer is shockingly bad on these. See stackoverflow.com/questions/3417074/…
@Franco: In the 2nd example you're providing four scalar values. No subquery needs to execute and comparing four ints is very fast.
Yep, a list of 4 scalars isn't a subquery, it just looks vaguely, sort of, like one
Martin, thank you for pointing me there, you rock! I searched but did not possess the proper 'language' to search for this. This explains it that it is a optimizer bug since otherwise it makes no sense at all. @Brian: I provided the 4 scalars as an example of what I imagined an ideal optimizer would do, which is execute the independent subquery first and cache the results; it would then behave like the scalar version I would think.
|
8

Yes, IN with subqueries is slow. Use a join instead.

SELECT
COUNT(DISTINCT subscriberid)
FROM em_link_data JOIN em_link ON em_link_data.linkid=em_link.id
WHERE em_link.campaignid = '2900' AND em_link.link != 'open'

And make sure you've defined indexes on em_link_data.linkid and em_link.id.

Improve this answer

3 Comments

Sorry, never done any benchmarking... just know this from experience.
The wird thing is if I change the subquery to the 4 values that it should return I go lightning fast. Is the optimizer messing up the order of execution?
@Scott - See stackoverflow.com/questions/3417074/…
5

The problem is that MySQL executes queries from outside to inside, while you might think that your subquery is done once and then its results are passed to the WHERE expression of the outer query (see MySQL documentation).

If you can't rewrite your query, you should do the following optimizations:

  • add an index on campaignid and link as FrustratedWithFormsDesigner said
  • check that the subquery uses indexes correctly by doing EXPLAIN SELECT ...
  • enable and tweak query cache, as that should speed up the subquery being called multiple times

One more idea would be to install MySQL proxy and write a little script that intercepts your query and rewrites it to use a join.

Improve this answer

2 Comments

This was eye opening to me - the fact that subqueries are actually run once for each row in the outer query is insane! Had been writing my queries assuming the opposite. Thank you!
Whatttt?!? I am reading it and not believing. Can you maybe explain what is the reason that it is working the opposite of what seem to be expected? Why from outside -> inside?
1

If your subquery is fast thus campaignid and link are absolutely indexed. l.id is PK and clustered thus is fast. But as far as I remember(from last time I checked this subject) , mysql describes about its internal optimizations for "in" subqueries to use the index sort of subquery result to improve performance and also uses cache for the left side of "IN" to drag it inside the subquery faster and if indexes are set true it must not have such difference to use inner join or "IN" rather than caching and it may be due to cache problem and massive data. http://dev.mysql.com/doc/internals/en/transformation-scalar-in.html

I don't know the situation of the software but if you can use INNER JOIN and you have (probably) some additional definitions before the IN clause in the WHERE clause of your outer query make sure to move that clauses to before of your main INNER JOIN via a temporary INNER JOIN behaves similar to an intervenient "where" clause sequentially and reduces the number of cross comparisons in a JOIN like this :

SELECT ... FROM t
INNER JOIN (SELECT 1) AS tmp ON t.asd=23
INNER JOIN t2 ON ...

Sample comparisons of normal and temp join lookups : 1000 * 1000 > 1000 + (100 * 1000)

Also it seems the subquery is filtered by constant vals thus if it was me I was gonna put the clauses in a sub query generating the resultset and reduce the number of comparisons in a JOIN like this :

SELECT ... FROM t
INNER JOIN (SELECT ... FROM t2 WHERE constant clauses) AS tbl2 ON ...

Anyway, in the "IN" query, comparing any column of the table in the subquery to any column of the table in outer query requires the columns of both side to be precisely indexed (regard to composite indexes) but still it may be a cache problem.

EDITED : Also I was curious to ask : Can the making a composite index on l.campaignid, l.link and l.id make any sense?

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.