Indexing for <= or >=
Posted by: Joe Koenig
Date: February 21, 2012 03:24PM

I'm trying to speed up a query on a database table that has about 2.7 million rows. The issue is really in the fact that the query is using comparison operators:

SELECT * FROM table WHERE low_zip <= '46526' AND high_zip >= '46526' AND scac = 'EXLA' ORDER BY low_zip DESC, high_zip DESC LIMIT 1;

Table engine is currently MyISAM. This table is used almost exclusively for reading.

On very nice hardware (2x quad core 2.66GHz Xeon, 32GB RAM, 15K RPM SAS 6Gb/s RAID 1), this query is taking over 1 second. The issue seems to be with the comparison operators. Does anyone have ideas for how to best index this table and improve the speed?


Options: ReplyQuote

Written By
Indexing for <= or >=
February 21, 2012 03:24PM
February 23, 2012 05:25PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.