Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Do you know that query will always return exactly one row? (Not quite sure from your description.) This might have an influence on what can be done to speed it up. Or it might not, I don't really know :) –  Mat Jan 18 '13 at 6:49
1  
From your question and fieldnames I deduce that the from_field and to_field define ranges and because you only expect one row that you know that these ranges do not overlap. The problem is that your database has no understanding of ranges and also doesn't know that they don't overlap. Result is that it is working under the assumption that many rows could match. With the indexes it is most likely collecting from each index speratly which rows match the condition of that index. That could be millions in each set. Then it lets through the rows they have in common. –  Eelke Jan 18 '13 at 7:05
    
Without indexes it would be forced to de a table scan which seems to be an easier operation in this case. For mysql I do not know a solution, postgresql has range types which should help in this situation. –  Eelke Jan 18 '13 at 7:10
add comment

1 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) ;
share|improve this answer
    
Do you know if adding a UNIQUE index on (from_field, to_field) would have any impact on the query compared to using LIMIT 1? –  Petter Brodin Jan 18 '13 at 11:01
1  
@PetterBrodin: I don't think the unique index would matter. The engine would still have to search the whole (or large part) of the index to find all matches. The condition has two range checks. It it was an equality check, it would be a different story. –  ypercube Jan 18 '13 at 11:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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