No index proposed when using .expert
(1) By anonymous on 2020-09-17 15:51:48 [source]
Hi all, brand-new to SQL so my apologies if the question doesn't make too much sense, please let me know and will try to clarify!
To help with my learning, I am trying to use the .expert functionality, and I came across the behaviour as in the min example below. To me it looks like .expert doesn't propose creating an index if a column is already part of an index, even if the query can benefit from a new one, but not the old one.
Is there any deeper reason (e.g. no actual difference in theoretical performance) related to how the multi-column index works, or it's just a limitation of the tool?
P.S. running version 3.28.0
sqlite> CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B));
sqlite> .expert --verbose
sqlite> SELECT * FROM example WHERE A=?; SELECT * FROM example WHERE B=?; SELECT * FROM example WHERE C=?;
-- Candidates -----------------------------
CREATE INDEX example_idx_00000043 ON example(C);
-- Query 1 --------------------------------
SELECT * FROM example WHERE A=?;
(no new indexes)
SEARCH TABLE example USING INDEX sqlite_autoindex_example_1 (A=?)
-- Query 2 --------------------------------
SELECT * FROM example WHERE B=?;
(no new indexes)
SCAN TABLE example
-- Query 3 --------------------------------
SELECT * FROM example WHERE C=?;
CREATE INDEX example_idx_00000043 ON example(C);
SEARCH TABLE example USING INDEX example_idx_00000043 (C=?)
sqlite> CREATE INDEX example_idx_b ON example(B);
sqlite> .expert --verbose
sqlite> SELECT * FROM example WHERE B=?;
-- Candidates -----------------------------
(null)
-- Query 1 --------------------------------
SELECT * FROM example WHERE B=?;
(no new indexes)
SEARCH TABLE example USING INDEX example_idx_b (B=?)
(2) By Dan Kennedy (dan) on 2020-09-17 17:02:18 in reply to 1 [link] [source]
Thanks for reporting this. Now fixed here:
(3) By Simon Slavin (slavin) on 2020-09-17 21:15:03 in reply to 2 [link] [source]
I love the fact that every time the team fixes something it adds a regression test to the test suite. I know this isn't new, but it's still good to see.
(4) By ddevienne on 2020-09-18 07:30:30 in reply to 2 [link] [source]
But SQLite has the SKIP-SCAN optimization, so recreating a 1-column index
on the 2nd column of an existing index may not always be the best option,
it depends on the cardinality of the 1st column of that 2-column index.