Building correct composite index for several columns MySql

Question

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.

You create covering index... but the 1st column must be prefix and the 2nd - either date_updated or end_date. Put company_id last. how indexes work with DISTINCT Not used. — Akina
– Akina, Commented Nov 16, 2020 at 12:47
From the info provided in the question there's no reason to suppose an index will make any difference at all. You could test and compare before and after adding index an run an explain to see if your index is used. — P.Salmon
– P.Salmon, Commented Nov 16, 2020 at 12:49
Also "index hints" can be used rather than adding/dropping indexes -- a faster way to test an index. — Rick James
– Rick James, Commented Nov 16, 2020 at 23:29

Rick James · Accepted Answer · 2020-11-16 23:31:10Z

1

Start each column tested with =, in any order: INDEX(prefix, ...).
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.
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.

edited Nov 16, 2020 at 23:31

answered Nov 16, 2020 at 23:25

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hairi Over a year ago

If DISTINCT and INDEX do not interact in my example, why need I company_id in the index?

Rick James Over a year ago

I added to make the INDEX "covering" -- and a slight performance improvement because the query can be performed entirely in the INDEX's BTree.

Collectives™ on Stack Overflow

Building correct composite index for several columns MySql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related