Given a schema like
CREATE TABLE `EventStatus`
(`id` varchar(1024) NOT NULL,
`node` varchar(255) DEFAULT NULL,
`lastUpdate` bigint DEFAULT NULL,
`deleted` integer DEFAULT 0,
`owner` varchar(1024) DEFAULT '',
`body` varchar(10240) DEFAULT NULL,
PRIMARY KEY (`id`, `node`) );
CREATE INDEX EventStatus_lastUpdate
on EventStatus (deleted, lastUpdate);
SQLite3 will use the index to count rows where deleted
, and delete rows where deleted = 1
, but not delete rows where deleted
. Why is this? Using indexed by
gives the "No query solution" error.
Using v3.31.1 CLI.
sqlite> delete from eventstatus where deleted='1';
QUERY PLAN
`--SEARCH TABLE eventstatus USING INDEX EventStatus_lastUpdate (deleted=?)
sqlite> select count() from eventupdates where deleted;
QUERY PLAN
`--SCAN TABLE eventupdates USING COVERING INDEX EventUpdates_lastUpdate
0
sqlite> delete from eventupdates where deleted;
QUERY PLAN
`--SCAN TABLE eventupdates
Using indexed by gives the "No query solution" error.
This is a clue. In short the specified index cannot be used (if forced).
This due to:-
To be usable by an index a term must usually be of one of the following forms:
column = expression
column IS expression
column > expression
column >= expression
column < expression
column <= expression
expression = column
expression IS column
expression > column
expression >= column
expression < column
expression <= column
column IN (expression-list)
column IN (subquery)
column IS NULL
column LIKE pattern
column GLOB pattern
i.e. deleted
is an inequality as opposed to deleted=1
or deleted='1'
to force the use of the index (using INDEXED BY ....
) is then an error (as it would if the EXPLAIN QUERY PLAN
were not used).
In the absence of the INDEXED BY
forcing the use of the index, then the query optimiser will decide how to locate the rows.
I believe the count() function comes into play as
EXPLAIN QUERY PLAN SELECT count() FROM eventstatus WHERE deleted:-
results in SCAN TABLE eventstatus USING COVERING INDEX EventStatus_lastUpdate
Whilst:-
EXPLAIN QUERY PLAN SELECT * FROM eventstatus /*INDEXED BY eventstatus_lastupdate <<<<< OUCH*/ WHERE deleted;
results in SCAN TABLE eventstatus
INDEXED BY is included then the
No query solution` results (hence the OUCH comment)You may wish to have a read of:-
In addition to the previously linked document.
You may also wish to consider using ANALYZE (if you feel that after reading the suggested links that you want the deleted
column to be the initial column of an index) according to the following snippets:-
Avoid creating low-quality indexes.. A low-quality index (for the purpose of this checklist) is one where there are more than 10 or 20 rows in the table that have the same value for the left-most column of the index. In particular, avoid using boolean or "enum" columns as the left-most columns of your indexes.
and
If you must use a low-quality index, be sure to run ANALYZE. Low-quality indexes will not confuse the query planner as long as the query planner knows that the indexes are of low quality. And the way the query planner knows this is by the content of the SQLITE_STAT1 table, which is computed by the ANALYZE command.
both from section 5 of the last linked document.
Some considerations re deleted
v deleted=1
or deleted='1'
IF the deleted column can only contain 0 (not deleted) or 1 (deleted) then the index would fit into the low quality basket so see above.
OTHERWISE
What if the value in deleted can be other than 0 or 1?
Yes the deleted=1
or deleted='1'
will be more efficient than just deleted
as the only a portion of the indexed rows will be selected BUT the result will be different to when using deleted as any rows that have a value that is not 1 will not match the selection criteria.
deleted
.Demo
Consider the following:-
DROP TABLE IF EXISTS `EventStatus`;
CREATE TABLE IF NOT EXISTS `EventStatus` (`id` varchar(1024) NOT NULL,`node` varchar(255) DEFAULT NULL,`lastUpdate` bigint DEFAULT NULL,`deleted` integer DEFAULT 0, `owner` varchar(1024) DEFAULT '', `body` varchar(10240) DEFAULT NULL, PRIMARY KEY (`id`, `node`) );
WITH
cte_counter(i,r) AS (SELECT 1,abs(random() % 7) UNION ALL SELECT i+1, abs(random() % 7) FROM cte_counter LIMIT 1000000)
INSERT OR IGNORE INTO eventstatus SELECT
'V'||i, /* ID column Vn WHERE n is 1 greater than the previous row */
'node'||(abs(random()) % 30) /* NODE column is based upon a partially random set*/,
abs(random()), /* LASTUPDATE column is just a positive random integer (as per SQLLite so 64bit signed) */
(abs(random()) % 10), /* DELETED column is randomly 0-9*/
'owner'||(abs(random()) % 200), /**/
'body'||abs(random()) FROM cte_counter
;
CREATE INDEX EventStatus_lastUpdate on EventStatus (deleted, lastUpdate);
SELECT 'a1',count(*) FROM eventstatus WHERE deleted;
SELECT 'a2',count(*) FROM eventstatus WHERE deleted='1';
SELECT 'a3',count(*) FROM eventstatus WHERE deleted=1;
SELECT 'a4',count(*) FROM eventstatus WHERE deleted>=1;
SELECT * FROM eventstatus WHERE deleted;
SELECT * FROM eventstatus /*WHERE deleted to show ALL rows */;
DROP TABLE IF EXISTS `EventStatus`;
A million rows are inserted according to the comments.
e.g. the rows will be like for example (last SELECT):-
However:-
delete=1
, is 99718 (not that far of 10%). Because only about 1 in 10 rows have 1 9/10ths will have either 0, 2,3 ... 9delete='1'
returns a count of 99718delete>=1
returns a count of 899900.Timings written to the message log, very much confirm when the index can be usefully/efficiently used:-
SELECT 'a1',count(*) FROM eventstatus WHERE deleted
> OK
> Time: 0.048s
SELECT 'a2',count(*) FROM eventstatus WHERE deleted='1'
> OK
> Time: 0.004s
SELECT 'a3',count(*) FROM eventstatus WHERE deleted=1
> OK
> Time: 0.003s
SELECT 'a4',count(*) FROM eventstatus WHERE deleted>=1
> OK
> Time: 0.022s
SELECT * FROM eventstatus WHERE deleted
> OK
> Time: 1.624s
SELECT * FROM eventstatus /*WHERE deleted*/
> OK
> Time: 1.833s
NOTE! using
EXPLAIN QUERY PLAN SELECT * FROM eventstatus WHERE deleted>=1;
As perhaps expected from the timings, (0.022) as opposed to (0.048 for WHERE deleted
), uses the index as >= is not an inequality as per:-
SEARCH TABLE eventstatus USING INDEX EventStatus_lastUpdate (deleted>?)