I wanted to run the following query:

-- Main Query    
SELECT COUNT(*) FROM table_name WHERE device_id IN 
     (SELECT DISTINCT device_id FROM table_name WHERE NAME = 'SOME_PARA')

This following query (sub query from Main Query):

SELECT DISTINCT device_id FROM table_name WHERE NAME = 'SOME_PARA'

executes in 7 seconds, giving 2691 rows from a table of 2.1M rows.

I fired the Main Query above and it is still executing after 5 mins+ of waiting.

Finally, I executed the sub query separately, took the 2691 records from the result, executed the following query:

-- Main Query (improvised)    
SELECT COUNT(*) FROM table_name WHERE device_id IN 
     ("device_id_1", "device_id_2", ....., "device_id_2691")

Surprisingly, this gave me an answer within 40 seconds.

What gives? Why doesn't MySQL use the same technique that I used and give an answer quickly? Am I doing something wrong?

mysql sql
Edit
Report