SQLite Forum

No index proposed when using .expert
Login

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:

https://sqlite.org/src/info/c666c85a433fbc83

(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.