Recently I tackled a query optimization problem, and I'm going to quickly go over the things we tried and the final result we went with.

I'll start with a little bit of background on the setup. Our database contains just under 700 tables and totals just over 100,000,000 records. We noticed that one part of our application was taking a little longer than we wanted it to and after some profiling we realized that we had a query that was taking approximately .83 seconds to complete.

The problem query:

 
SELECT rt FROM myTable
    WHERE origin_zip_low <= ?
    AND origin_zip_high >= ?
    AND dest_zip_low <= ?
    AND dest_zip_high >= ?
    LIMIT 1";

Now this table contains roughly 842,xxx rows and this zips are all integers up to 5 digits. I tried using a single index on:

 
ALTER TABLE `myTable` ADD INDEX ( `origin_zip_low` , `origin_zip_high` , `dest_zip_low` , `dest_zip_high` ) ;
 

Now using an explain on this query and index, it never seemed to want to use the index.

So then we indexed each separately and had 4 separate indexes.

 
ALTER TABLE `myTable` ADD INDEX ( `origin_zip_low` );
ALTER TABLE `myTable` ADD INDEX ( `origin_zip_high` );
ALTER TABLE `myTable` ADD INDEX ( `dest_zip_low` );
ALTER TABLE `myTable` ADD INDEX ( `dest_zip_high` );
 

We then ran the queries with many different zip code pairs and found that it sometimes used one of the indexes, sometimes it was dest_zip_low, sometimes origin_zip_low, etc, but most of the time it still didn't use the index at all. So without the index the queries took about .83 seconds and when it did use one of the indexes it ran in about .3 seconds.

Now you would think that MySQL would pick an index and use it, but sometimes MySQL will not use an index, even if one is available. The optimizer estimates that using the index would require MySQL to access a large percentage of the rows in the table.

After doing some reading online I found that doing queries with 'greater than or equal to' or 'less than or equal to' are best done using HASH table indexes. InnoDB does not support HASH table indexes and neither does MyISAM, however the MEMORY storage engine does.

When you initially look at the MEMORY storage engine you might be turned away due to the fact that your data lives in memory, and then once MySQL is restarted, your table will be empty. The good thing for us in this case though is our data essentially is static and it doesn't change, it's simply a lookup table so we can have our table myTable which is static and always contains our data, and then myTable_memory which we'll load the data from myTable.

Another thing with using the MEMORY storage engine is the data that goes into the table obviously will be loaded into your server memory, you must have the memory available to use. Our server we're using has 10 Gigs of memory currently, and the server is dedicated to this one application so after loading 'myTable' into memory it only takes up 80 Megs.

Configuration Changes

MySQL stores the tables using the MEMORY storage engine in a heap table, and since MySQL gives you a 16M 'max_heap_table_size', and since our table is larger than that we'll increase this value in our my.cnf. Also since every time our server is restarted that table will be empty, we want to populate the table when mysql starts up, so we'll set an init script to do just that:

 
#my.cnf
max_heap_table_size = 100M
init-file = /usr/local/www/vhosts/mysql_init.sql
 

Now in the mysql_init.sql we simply have this:

 
#mysql_init.sql
USE myDatabase;
INSERT INTO myTable_memory SELECT * FROM myTable;
 

The result, after converting the table to the MEMORY storage engine the queries never took longer than .09 seconds.

Conclusion

In this case using the MEMORY storage engine was huge since we were able to take advantage of the HASH index which aided in performing the query very quickly, however using MEMORY tables will not always save you time, we tried this approach on other queries and found absolutely no performance gain, but if you're doing searches using greater than or equal to is where it really shines.

By no means am I a MySQL DBA, so if you are, and you have more insight into this, please leave a comment!