Skip to content

MySQL Choose Index Column Order

Based on blog post.

The most selective column should be first in the index, so that the number of possible matching rows is the smallest.

  1. Get the row amounts of each WHERE condition.
    Use a read replica on production when possible.
select sum(columnA='testA'), sum(columnB='testB'), ... from table
  1. Move the condition with the lowest row count into the WHERE.
select sum(columnB='testB') from table where columnA='testA'

Other Index related tools:

#published

Links to this note

These notes are unpolished collections of thoughts, unfinished ideas, and things I want to remember later. In the spirit of learning in public, I'm sharing them here. Have fun exploring, if you want!
© 2020 by Adrian Philipp