I have a table with about ~200 million rows looking like this:
id, name, timestamp, class
I have an index on each column and I am trying to run a query like this:
SELECT name FROM t WHERE timestamp >= date_x AND timestamp < date_y AND class='class_a'
This query only uses the index on
class and when
date_y is about a month a part EXPLAIN says that it looks at about 12.5 M rows which is less than for the index on
When the date range is small this query obviously returns fast but as the range increases this query soon gets stuck and never even starts sending any data (i.e stuck on
Sending data), compared to a simple
SELECT * FROM t which in
SHOW PROCESS list alternates between
Sending data and
Writing to net.
My current workaround is to fetch rows within a daily range and then concatenate the results. For a month it should return about 150 K rows which I think should be doable for a straight-forward query such as this. That is why I think that there should be a way to do this without the workaround.
I have tried looking at the settings but have found no apparent mistakes there either. I can update this question with the relevant MySQL settings.
- Do I need a multicolumn index on both
- Why cannot MySQL use both indexes to limit the search?
- Have I missed something?
1 SIMPLE table ref index_class,index_timestamp index_class 302 const 11898928 100.00 Using where