There is no datetime type in SQLite. You are storing ISO Text representations so you should declare the column as TEXT.
An Index is only useable for a prefix of equality contstraints followed by ONE inequality constraint. From your index (model_id, confidence, ts) and your query constraints ( '=', '>', 'BETWEEN') this means that model_id is useable (equality constraint) and confidence (inequality constraint>), which allows the Engine to locate the first record with model_id == 1 and confidence > 0.8 and partial scan the table until the model_id changes. Note that the ts field is not guaranteed to be ascending within this interval (a record with higher confidence but smaller timestamp may follow any given record in the scann portionof the table. Thus the ts constraint needs to be handled without the index. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Hamesh Shah Gesendet: Mittwoch, 24. Oktober 2018 00:45 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] Index help... 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 ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
