Covering Index with ORDER BY

Question

If you have a composite clustered index say: (ClientId, Date, OrderId, ProductId). (All fields are non-null)

Given the query:

SELECT * FROM products WHERE ClientId = 33 AND OrderId = 4 AND ProductId = 2 ORDER BY Date

Will this query take advantage of the covering index fully by having the ORDER BY Date or does it require that the Date field be in the WHERE clause?

I added tags of the main database engines, in case there is discrepancy between each.

A covering index means that all columns in the query (including those in the `Select` clause) are in the index. So, the column `Date` needs to be in the index too. Quick aside: If a person saw this query and the index definition, they would not know whether it is a covering query. It would be more clear if you use `Select ClientId, OrderId, ...` instead of `Select * ...` -- this is widely considered a best practice for production code. — Mike, Oct 06 '15 at 21:43

score 1 · Accepted Answer · answered Oct 06 '15 at 21:24

1

For MySQL, your index can't be used. MySQL's indexes apply left->right:

(ClientId, Date, OrderId, ProductId)

since your query involves only ClientID and ProductID, the index can't be used - you'd have to use include Date and OrderID in the query as well. Note that the specific ordering of the fields in your query is irrelevant - it's whether they're being used as all that counts:

So if your query's where has:

clientid                     -> usable
clientid, date               -> usable
date, clientid               -> usable, order of usage irrelevant
clientid, orderid            -> not usable, missing date
clientid, orderid, productid -> also not usable, missing date
clientid, productid          -> not usable, missing orderid, missing date
date                         -> not usable missing clientid

Note that this is for mysql only. Some other DB systems do not have this restriction.

answered Oct 06 '15 at 21:24

Marc B

356,200
43
426
500

Its worth nothing that I include `OrderId` so that only `Date` was in question, in which case its in the `ORDER BY` and not the `WHERE`. – LearningJrDev Oct 06 '15 at 21:25
in that situation, I'm not sure how it works. `where` and `order` are two distinct phases of the query, so using a field in one may not help if the field isn't used in the other. regardless, you can see what mysql's execution plan is by `explain`ing your query. if the index can be used, it'll show up – Marc B Oct 06 '15 at 21:27
can you use `Date is not null` in the date sequence? or Date = Date or anything that doesn't require much logic – JamieD77 Oct 06 '15 at 21:29
Yes `Date` cannot be NULL, nor can any of the other fields. – LearningJrDev Oct 06 '15 at 21:31

score 0 · Answer 2 · answered Oct 06 '15 at 21:27

0

It really depends on many things , like

how many rows in the table ?
how much time it takes for to get values from index leaf ?
What is inside the * that you are getting from ...

etc.

Thats why they invented "explain plan"

Use it when you want to know

answered Oct 06 '15 at 21:27

Eveis

202
1
2

I understand the query optimizer may do what it wants depending on many factors, the question was in general to better understand the concept. Also it wouldn't be feasible to try this on the table I had in question, as it has 200 million records and testing out indices would take more time than getting a better understanding from SO. – LearningJrDev Oct 06 '15 at 21:30

score 0 · Answer 3 · edited May 23 '17 at 11:58

0

To add the the discussion, I actually found the answer after 10 minutes of searching stack (Hard to find because of the generic terms involved). Seems this answer: Proper field orders for covering index - MySQL

Says that for MySQL SELECT WHERE GROUP BY HAVING ORDER BY all are valid references.

I wonder if the same is true for MSSQL and Oracle

edited May 23 '17 at 11:58

Community

1
1

answered Oct 06 '15 at 21:28

LearningJrDev

911
2
8
27

score 0 · Answer 4 · answered Oct 06 '15 at 23:26

Re-order the columns in your index to ClientId, OrderId, ProductId, Date

Then the index will be used.

And no, an order by will not help out the use of an index. But an order by can be helped by using an index,

If its acceptable you could change your order by to be ORDER BY ClientId, OrderId, ProductId, Date Then it would use the same index

Or if you did not change the order of the columns in the index then you can change the order by to be ORDER BY ClientId, Date and that would use the index

Covering Index with ORDER BY

4 Answers4