Hi,
The SQLite documentation does a good job explaining what an 'automatic index'
is [1], so when someone looks at 'explain query plan' output and sees something
like this they get worried:
SEARCH TABLE fmeta USING INDEX sqlite_autoindex_fmeta_1 (file_id=?)
The link above[1] uses #autoindex, the log code associated with it [3] is
called SQLITE_WARNING_AUTOINDEX, so you can see why this is a bit confusing.
The SQLite source code explains that 'sqlite3_autoindex_*' is the index created
for PRIMARY KEY and UNIQUE constraints (i.e. a persistent index created at
table creation time):
** If pName==0 it means that we are
** dealing with a primary key or UNIQUE constraint. We have to invent our
** own name.
/* This routine has been called to create an automatic index as a
** result of a PRIMARY KEY or UNIQUE clause on a column definition, or
** a PRIMARY KEY or UNIQUE clause following the column definitions. */
Indeed the column from the query plan is part of a PRIMARY KEY, and there was
no warning logged via SQLITE3_CONFIG_LOG so there is nothing to worry about:
CREATE TABLE fmeta (file_id INTEGER NOT NULL REFERENCES files(fid) ON DELETE
CASCADE ON UPDATE CASCADE, key TEXT (256) NOT NULL, value BLOB (1024) NOT NULL,
PRIMARY KEY(file_id, key));
I would suggest adding an explanation about sqlite3_autoindex_ to the
documentation.
For example add to [1]:
-- example --
You can also use [EXPLAIN QUERY PLAN][2] to check for automatic index usage at
query preparation time.
-- example --
And add to [2]:
-- example --
1.5 Automatic indices
SQLite may create automatic indices in certain situations [1]. You can use
EXPLAIN QUERY PLAN to check for this, following the example from [1]:
sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a=c;
sele order from deta
---- ------------- ---- ----
0 0 0 SCAN TABLE t1
0 1 1 SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX (c=?)
You can create a persistent index so that SQLite doesn't have to create a new
index at every query:
sqlite> CREATE INDEX my_persistent_index ON t2(c);
sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a=c;
sele order from deta
---- ------------- ---- ----
0 0 0 SCAN TABLE t1
0 1 1 SEARCH TABLE t2 USING INDEX my_persistent_index (c=?)
Or if your entries in the column are unique you can use a primary key or unique
constraint with same effect:
sqlite> CREATE TABLE t3(c PRIMARY KEY,d);
sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1, t3 WHERE a=c;
sele order from deta
---- ------------- ---- ----
0 0 0 SCAN TABLE t1
0 1 1 SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1
(c=?)
Note that sqlite_autoindex_* is the name given by SQLite to indexes created
automatically as a result of a PRIMARY KEY or UNIQUE clause.
-- example --
[1]: https://www.sqlite.org/optoverview.html#autoindex
[2]: https://www.sqlite.org/eqp.html#autoindex
[3]: https://www.sqlite.org/rescode.html#warning_autoindex
--
Edwin T?r?k | Co-founder and Lead Developer
Skylable open-source object storage: reliable, fast, secure
http://www.skylable.com
