I’m not the expert here, but it appears that the cause is that your looking for things greater than some confidence. This forces an index scan. There’s nothing that gives a list of different confidences greater than, in this case .8, but even if it did, an index scan might be faster than individual lookups.
The DB has no way of knowing that, after it reaches your high timestamp that it should skip to the next confidence value, since it doesn’t know what that next value is. So, it has to read all the index records, making the timestamp part of the index only used for the value. As an example, perform your lookup use confidence = .8 and you may see ta being used. > On Oct 23, 2018, at 6:48 PM, Hamesh Shah <ham...@evorta.com> wrote: > > 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 Confidentiality notice: This e-mail is intended solely for use of the individual or entity to which it is addressed and may contain information that is proprietary, privileged, company confidential and/or exempt from disclosure under applicable law. If the reader is not the intended recipient or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender by reply e-mail or collect telephone call and delete or destroy all copies of this e-mail message, any physical copies made of this e-mail message and/or any file attachment(s). _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
