SQL Indexing not improving speed

Question

Given an SQL database with index for (startpage, endpage), with a relation Articles(*ID*, title, startpage, endpage), why isn't a query

SELECT title 
FROM Articles 
WHERE endpage = 100;

sped up by the previous index?

Apologies for this peculiar question as it came from a practice paper without explanation.

you need to either create separate index `(endpage, title)` or at least swap `(endpage, startpage)` in existing one — Iłya Bursov, Apr 13 '22 at 17:25
Could you please specify RDBMS you use. One explanation can be that table Articles is very small and engine does not need to use index — Sergey, Apr 13 '22 at 17:26

score 2 · Accepted Answer · answered Apr 13 '22 at 17:27

2

Why the index shown does not help that query: because endpage is not the leftmost column of the index.

See my answer to Does Order of Fields of Multi-Column Index in MySQL Matter for more explanation (that question is about MySQL, but the answer should apply to any implementation of B-tree indexes).

answered Apr 13 '22 at 17:27

Bill Karwin

Thank you for that excellent explanation. If I could ask an extension of this question, for an index (a,b), would a query asking (WHERE a=1 AND b=2) be different from (WHERE b=2 AND a=1)? It seems that the 2nd query would be longer unless sql processes AND queries differently – vernal123 Apr 13 '22 at 17:34
Some dbms's have skip-scan. I.e. can take advantage of the index even for its second column. – jarlh Apr 13 '22 at 17:36
@jarlh, Thanks, that's a good point, the OP did not specify which product they use. – Bill Karwin Apr 13 '22 at 17:45
@vernal123, It depends on the implementation, but in products I've used, the order of `AND` terms wouldn't matter in the example you show, because the optimizer knows that `AND` expressions are commutative. Use EXPLAIN to test the optimizer plan on your database. – Bill Karwin Apr 13 '22 at 17:46
Apologies! Because this question came from a practice paper, there was no DBMS specified. In our labs we have been using mysql however – vernal123 Apr 13 '22 at 17:47
@jarlh, MySQL 8.0.13 is the first version that supports skip-scan, but I think a search would be optimized much better if the column(s) to be searched were the leftmost columns of the index. – Bill Karwin Apr 13 '22 at 18:05
@BillKarwin, absolutely. Skip-scan is just the second choice. – jarlh Apr 13 '22 at 18:08

1 Answers1