Extremely Slow Nested mySQL Query

Question

So, I've just begun a new mySQL database with two main tables, bigTable[600,000 lines] and lilTable[8000 lines]. There is an field blId which links the tables, which has been indexed in bigTable.

I want to select all entries from the bigTable that share a blId with any entry from the lilTable that matches certain criterion, but I don't need any other information from that table, so a join seems excessive.

Without further ado, here is my terribly slow query:

SELECT * FROM testdb.bigTable where blId in 
(SELECT blId FROM certtest.lilTable WHERE color LIKE 'blue');

This takes ~52 seconds to run on my computer, and still took 50 seconds when the inner query returned 0 results! On the contrary, if I run the inner query separately and manually create a list of acceptable blId's that I put in place of the sub-query, it runs in less than a tenth of a second. What in the blue blazes is going on here?

Edit: So I've found one way to speed it up, wrap it in another, redundant select statement? (Cuts query time down to 0.25 sec) It anyone could explain this behavior, it would be greatly appreciated.

SELECT * FROM testdb.bigTable where blId in 
(SELECT * FROM 
  (SELECT blId FROM certtest.lilTable WHERE color LIKE 'blue') AS why
);

Do you have to use LIKE in the sql? Would it be quicker if you change to color='blue' ? Please include table structure. — Tan Hong Tat
– Tan Hong Tat, Commented Jul 20, 2017 at 3:18
Do you have indices defined on the tables? How large are the tables? Why use Like? — ChuckCottrill
– ChuckCottrill, Commented Jul 20, 2017 at 3:39

Gordon Linoff · Accepted Answer · 2017-07-20 02:35:28Z

1

Try using exists instead:

SELECT bt.*
FROM testdb.bigTable bt
WHERE EXISTS (SELECT 1
              FROM certtest.lilTable lt
              WHERE lt.color LIKE 'blue' AND bt.blID = lt.blId
             );

For the purposes of this query, you want an index on lilTable(blId, color).

answered Jul 20, 2017 at 2:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Carl Shiles Over a year ago

Still basically the same speed.

Gordon Linoff Over a year ago

@CarlShiles . . . Do you have the specified index?

Carl Shiles Over a year ago

I do, however I didn't include it in the example because an index is obviously not the problem when nesting the expression in an additional select speeds it up 100 fold. I am looking for someone who has under the hood mySQL knowledge who can explain this behavior.

Collectives™ on Stack Overflow

Extremely Slow Nested mySQL Query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related