Hello,
I have this query:
SELECT
(SELECT id FROM meta2 WHERE (meta2.parameter, meta2.value)=(a.parameter, a.value)) AS metaid,
t.id AS traceid,
NULL AS itemid,
a.id AS oldid
FROM meta a
LEFT JOIN trace t ON a.datasetid=t.datasetid
WHERE a.itemid IS NULL AND a.traceid IS NULL AND a.datasetid IS NOT NULL;
It takes about 45 seconds and I'm wondering why.
An EXPLAIN QUERY plan gives the following result:
QUERY PLAN
|--SEARCH TABLE meta AS a USING INDEX metadata_idx_05 (traceid=? AND itemid=?)
|--SEARCH TABLE trace AS t USING COVERING INDEX trace_idx_03 (datasetid=?)
`--CORRELATED SCALAR SUBQUERY 1
`--SCAN TABLE meta2
So why is it using "SCAN TABLE meta2" without an index?
Trying to find the problem, I have created almost any index:
CREATE INDEX idx_meta2_parameter_value on meta2 (parameter, value);
CREATE INDEX idx_meta2_value on meta2 (value);
CREATE INDEX idx_meta2_parameter on meta2 (parameter);
CREATE INDEX idxnc_meta2_parameter on meta2 (parameter collate nocase);
CREATE INDEX idxnc_meta2_value on meta2 (value collate nocase);
CREATE INDEX idxnc_meta2_parameter_value on meta2 (parameter collate nocase, value collate nocase);
CREATE UNIQUE INDEX uidxnc_meta2_parameter_value on meta2 (parameter collate nocase, value collate nocase);
CREATE INDEX idxnc_meta2_id_parameter_value on meta2 (id, parameter collate nocase, value collate nocase);
Why does the query not use any index?
The table declaration is:
CREATE TABLE meta2 (
id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
parameter TEXT NOT NULL COLLATE NOCASE,
value TEXT NOT NULL COLLATE NOCASE,
UNIQUE (parameter, value)
ON CONFLICT IGNORE
);
Results are the same in versions 3.31.1 and 3.30.1.