MySQL – indexes on unique data can slow down your query


Today I was running a query that joined two tables with one table having 1.9 million records and the other 340K. I had put an index on two columns that contained unique data and the query took 1.37 seconds to complete. By removing these indexes, the completion time was reduced to 0.25 seconds. The indexes did nothing for performance since the all the data was already unique. What surprised me was how much slower it made the query though.

SELECT * FROM geo_city_ip_block b INNER JOIN geo_city_ip_location l USING(location_id) 
WHERE '1142362046' BETWEEN b.start_ip AND b.end_ip
/* without indexes: 0.25 */
/* with indexes: 1.37 */

It’s worth noting that I was able to further improve the performance of this query by not using MySQL’s “between” clause.

SELECT * FROM geo_city_ip_block b INNER JOIN geo_city_ip_location l USING(location_id) 
WHERE b.start_ip < '1142362046' AND b.end_ip > '1142362046';
/* without indexes: 0.16 */

Leave a comment

Your email address will not be published. Required fields are marked *