How to create indexes in the right way

Categorized as Databases Tagged

The science of creating database indexes is learned from experience and the right people, not to mention much more complicated multiple-column indexes. But it wasn’t supposed to be complex! Here are three simple pieces of advice for the column ordering of multiple column indexes, solving 90% of the performance issues.

Three rules for Multiple-Column Indexes are:

  • Columns with equality checks are first
  • Columns with most different values are first
  • Range columns (no equality check) are last
SELECT *
FROM shop_articles
WHERE tenant_id = 6342 AND category = 'books' AND price < 99.99;

CREATE INDEX shop_articles_key ON shop_articles (
  tenant_id, -- equality-check = true, most-different-values = true
  category, -- equality-check = true, most-different-values = false
  price -- equality-check = false
);

Leave a reply

Your email address will not be published.