CODEWITHSUNDEEP
mysqlindexing
Hairi
Hairi

Reputation: 3715

Building correct composite index for several columns MySql

I want to build composite index that will optimise the following query

SELECT DISTINCT company_id FROM datavalues 
WHERE prefix = 'SON'
AND date_updated < '2020-07-15 23:59:59'
AND end_date > '2020-07-15';

Is this the most optimal index? Im confused how indexes work with DISTINCT

CREATE INDEX index_name
ON datavalues (company_id ,prefix ,date_updated, end_date );

Please help. I am new to Mysql indexes.

Upvotes: 0

Views: 47

Answers (1)

Rick James
Rick James

Reputation: 142218

  1. Start each column tested with =, in any order: INDEX(prefix, ...).
  2. Then move on to one range: INDEX(prefix, date_updated, ...) or INDEX(prefix, end_date, ...). Include both; let the Optimizer discover which will be better.
  3. Finally, consider making the index "covering", as you did.

So, I recommend providing two composite, covering, indexes:

INDEX(prefix, date_updated, end_date, company_id)
INDEX(prefix, end_date, date_updated, company_id)

(Putting company_id first is not good -- it won't help with the WHERE, and barely helps with the "covering".)

More cases and discussion: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

DISTINCT is a dedupping pass between WHERE and ORDER BY. (Also, DISTINCT is mostly redundant with GROUP BY.) DISTINCT and INDEX do interact, but not in your example.

Caveat: If you change anything in that query, my advice may be nullified.

Upvotes: 1

Related Questions