MySQL Multiple-Column Indexes: Order Matters

Posted on in Programming

Powered By MySQLThis will just be a short reminder. If you're planning to create multiple-column indexes in MySQL, remember that the order of the columns matters. As the MySQL documentation points out, any "leftmost prefix of the index can be used." This means that the index can be used for any number of columns in a where clause as long as those columns are at the beginning of the index column list and in the same order.

I was recently reminded of this issue while debugging a performance problem. Adding a multiple-column index resulted in query performance improvement of 400% to 2,000%. Naturally, this result is highly specific to the problem we were trying to solve.

My Bookshelf

Reading Now

Other Stuff