CODEWITHSUNDEEP
sqlsqlite
user8370684
user8370684

Reputation:

Do I have a redundant index in this SQLite schema?

I created an SQLlite schema as follows:

CREATE TABLE tab1 (
        year INTEGER,
        tar_id TEXT,
        content BLOB,
        UNIQUE (year, tar_id) ON CONFLICT REPLACE);
CREATE INDEX tab1_ix1 ON bcas (year, tar_id);

Then I looked at a query plan:

sqlite> explain query plan select * from tab1 where tar_id = 1 and year = (select max(year) from tab1 where year < 2019 and tar_id = 1);
QUERY PLAN
|--SEARCH TABLE tab1 USING COVERING INDEX sqlite_autoindex_tab1_1 (year=? AND tar_id=?)
`--SCALAR SUBQUERY
   `--SEARCH TABLE tab1 USING COVERING INDEX tab1_ix1 (year<?)

It seems to me that only one index would be sufficient to do this, but it uses both my explicit tab1_ix1 and and automatically generated sqlite_autoindex_tab1_1.

Is one of them redundant? If so, how do I get rid of one of them and get the same behaviour?

Upvotes: 0

Views: 77

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Yes, you have a redundant index. A unique constraint generates an index automatically. You do not need to create another one explicitly index for the same columns in the same order.

Note that an index on (tar_id, year) would be a different index, because the ordering of keys in the index matters.

Upvotes: 2

Ahmed Mustafa
Ahmed Mustafa

Reputation: 29

Although both seam redundant but there is some detail to be focused. In first part of query "*" is used so the auto created candidate is best suited because it links all other columns to retrieve. While in second portion with "max" clause only two columns (year, tar_id) are under consideration and they are all present with needed sequence in "smaller" manually created index "tab1_ix1" and engine thinks smaller being efficient so it uses tab1_ix1 this time.

So the default auto created index will be utilized by engine if slight degradation in performance is affordable and second smaller tab1_ix1 seem burden maintenance wise.

Upvotes: 0

Related Questions