I need a little help with some strange indexing behaviour. I have a table called detected.
i create a index for: id integer, confidence ASC, timestamp ASC Then when I query with a simple select from where with integer, then confidence, then timestamp in order, for some reason the timestamp index isn't used ? SEARCH TABLE detected USING COVERING INDEX detected_model_id_confidence_ts (model_id=? AND confidence>?) I read the website, I tried it many times around and still no joy. I can't see why it's not using the timestamp that is already ordered for my sql ts > and ts < statement. Python versions: sqlite3.version 2.6.0 / python api version. *sqlite3.sqlite_version 3.24.0* table standalone: CREATE TABLE detected ( id INTEGER PRIMARY KEY, model_id integer NOT NULL, state_id integer NOT NULL, dataset_id integer NOT NULL, class_id integer NOT NULL, confidence REAL NOT NULL, ts DATETIME NOT NULL, x0 INTEGER NOT NULL, y0 INTEGER NOT NULL, x1 INTEGER NOT NULL, y1 INTEGER NOT NULL, file_id INTEGER NOT NULL ) index creation: CREATE INDEX `detected_model_id_confidence_ts` ON `detected` ( `model_id`, `confidence` ASC, `ts` ASC ); I can't see the timestamp being used: explain query plan select distinct ts from detected where model_id = 1 and confidence > 0.8 and ts > '2018-10-10 01:25:25' and ts < '2018-10-23 08:10:17' _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
