Simple SELECT-query on big table gets stuck in "Sending data" state

Question

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_x and 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 timestamp.

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 class and timestamp?
• Why cannot MySQL use both indexes to limit the search?
• Have I missed something?

EXPLAIN output:

1   SIMPLE  table   ref index_class,index_timestamp index_class 302 const   11898928    100.00  Using where

Answer 1

I believe that for a small interval the index chosen by the query optimizer is the one that uses the timestamp, so it is faster.

I think a composite index class, timestamp would solve the problem.

Ps: Can you place the query plan when the range is small?

Answer 2

Your query haven't appropriate index (only simple index for class is used) and thus is forced to fetch 11898928 rows to apply the timestamp restriction.

Add the multicolumn index (class, timestamp) and you'll see what happens.

Please post the new EXPLAIN output to show the difference.