0

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
  • 1
    Can you show the EXPLAIN's output? – Kondybas Jul 28 '17 at 10:03
  • Done, @Kondybas! – Parham Jul 28 '17 at 15:19
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?

| improve this answer | |
1

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.