Mysql – Slow Query Gets Even Slower After Indexing

database-tuningMySQLperformancequery-performance

I have a MySQL database with just one table containing 8 million records. I need to query the table to find a single record that matches a specific number:

select * from my_table 
where 
  (from_field <= 1533177608)
and 
  (to_field   >= 1533177608)

Without an index, the query runs in 5 seconds. But surprisingly, when I create an index on two queried columns, the query takes 35 seconds to complete:

alter table my_table 
add index test (from_field, to_field);

It is very important for me to speed up the query to reach a fraction of a second. What can I do to improve it?

Best Answer

If you know that the result will be no more than one rows - or if you don't care, you just want one row even if there are more than one - you can add LIMIT 1 to the ORDER BY clause (you can even leave it without ORDER BY):

select * from my_table 
where 
  (from_field <= 1533177608)
and 
  (to_field   >= 1533177608)
ORDER BY 
    from_field DESC
LIMIT 1 ;

or 

select * from my_table 
where 
  (from_field <= 1533177608)
and 
  (to_field   >= 1533177608)
ORDER BY 
    to_field ASC
LIMIT 1 ;

or just:

select * from my_table 
where 
  (from_field <= 1533177608)
and 
  (to_field   >= 1533177608)
LIMIT 1 ;

The first version above (and probably the 3rd one) will do an index scan on the (from_field, to_field) index, until it finds one match and then stop (and read the related row from the table).

The above queries will still be not very efficient when there is no match at all, as a large part of the index will have to be scanned.

If you are 100% sure that there are no overlaps of the intervals, you can use this version, which will only need an index on (from_field) and do an index seek only:

SELECT * FROM my_table
WHERE from_field =
  ( select MAX(from_field) from my_table 
    where 
      (from_field <= 1533177608)
  )
  AND 
    (to_field   >= 1533177608) ;
Related Question