By Adrian Philipp
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.
- 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
- Move the condition with the lowest row count into the WHERE.
select sum(columnB='testB') from table where columnA='testA'
Other Index related tools:
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!