How to create indexes in the right way

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
);