SQLite Forum

No index proposed when using .expert
Login
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=?)
```