MySQL - Varied query runtime for 'IN' Clause

Asked Dec 26 '17 at 11:44

Active Dec 26 '17 at 12:30

Viewed 134 times

I have a table of about million rows and I am trying to filter out rows based on a particular column using an IN clause.

Sample data:

status
---------
'XXX-01'
'XXX-02'
'XXX-06'
'XXX-010'
'XX-XXX-027'
'XX-XXX-030'

Query:

The following query took about 5 seconds to complete.

select * from demo_tabl where substring_index(status, '-', -1) in ('06', '07');

But the following took just a few milliseconds to complete.

select * from demo_tabl where substring_index(status, '-', -1) in ('06', '07', '030', '027');

The column is indexed, however, the index goes unused due to the substring_index function.

Question:

Why is there a huge difference in the query runtime, in spite of the similar formation?

edited Dec 26 '17 at 12:30

asked Dec 26 '17 at 11:44

Sathiya Sarathi

Are you measuring the time until the first row is returned or until all rows are returned? – Gordon Linoff Dec 26 '17 at 11:50
I think you should use having clause with group by instead of where clause – Rahul Dec 26 '17 at 11:50
2

Did you run each query, just once, in this order? If so the data will be cached as a result of running the first query. How many rows are returned in each case? – Ron Ballard Dec 26 '17 at 11:54
@GordonLinoff, I was measuring it until limit 21 – Sathiya Sarathi Dec 26 '17 at 12:27
@rahulsm, my question is.. why the time difference? – Sathiya Sarathi Dec 26 '17 at 12:27
@Ron Ballard, Same, as well as during different times.. observed the varied performance for both.. – Sathiya Sarathi Dec 26 '17 at 12:28
Run the first query 3 times. Run the second query 3 times. Then fist query again 3 times. Then second query again 3 times. Report the time it took to execute each of the 12 queries. – jcaron Dec 26 '17 at 12:32
@jcaron, the time still applies the same, just as reported in the question.. – Sathiya Sarathi Dec 26 '17 at 12:40
@SathyasarathiGunasekaran so every single time the first query takes 5 seconds and the second one milliseconds? What does `EXPLAIN` report for each of the queries? – jcaron Dec 26 '17 at 12:43
@jcaron The index is being used using 'where' clause.. – Sathiya Sarathi Dec 26 '17 at 12:47
@SathyasarathiGunasekaran is the explain the same for both queries? – jcaron Dec 26 '17 at 15:41
Yes, it is.. @jcaron – Sathiya Sarathi Dec 27 '17 at 07:18
I don't see the `LIMIT 21`; what do you mean? With `LIMIT 21`, the second query might stop a lot sooner due to finding lots of `030` early in the table! – Rick James Dec 28 '17 at 00:40
which means even if i limited it to 21 entries.. the runtime still is the same – Sathiya Sarathi Dec 28 '17 at 19:11

MySQL - Varied query runtime for 'IN' Clause

0 Answers0