Description
create index ix1 ON default (x DESC , y, z); |
create index ix2 ON default (z,y,x); |
|
SELECT *
|
FROM default |
WHERE x IS NOT NULL AND y = 5 AND z = 10 |
ORDER BY x DESC
|
OFFSET 0 LIMIT 10; |
ix1 is better index because it can use index order and do pagination.
Both indexes as same index keys only difference order and collation.
For given query the number index keys can pushdown (longest sargable keys) on each index is same, one of them is eliminated randomly.
Can we keep both indexes and see if any further index pushdown is possible? Yes.