Change your query to explain query plan select * from Disruptions where status = 2 collate nocase OR status = 6 collate nocase;
to match your index. > -----Original Message----- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Rob Willett > Sent: Friday, 17 March, 2017 04:20 > To: SQLite mailing list > Subject: [sqlite] Yet Another Why Doesn't Sqlite Use My Index question ... > > Hi, > > We've been struggling with a complex query that we have written. One of > the elements of this complex query is a select statement that doesn't > appear to use an index when we *think* it should do. We're not going to > ask you to debug a large complex SQL query (unless you have nothing else > to do today) but we're working our way through the query trying to > understand where we've gone wrong. So we've broken down the query and > are trying each section to see what it performs (or rather doesn't > perform like). > > The sub query is a simple select on a large table, Disruptions. The > Disruptions table has 180M rows of data. > > The schema for it is here. We've pulled it straight from Navicat for > SQLite. > > ``` > CREATE TABLE "Disruptions" ( > "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, > "version" integer NOT NULL, > "Disruption_id" INTEGER NOT NULL, > "status" integer NOT NULL, > "severity" integer NOT NULL, > "levelOfInterest" integer NOT NULL, > "category" integer NOT NULL, > "subCategory" integer NOT NULL, > "startTime" TEXT NOT NULL, > "endTime" text NOT NULL, > "location" integer NOT NULL, > "corridor" integer NOT NULL, > "comments" integer NOT NULL, > "currentUpdate" integer NOT NULL, > "remarkTime" TEXT NOT NULL, > "lastModTime" TEXT NOT NULL, > "CauseAreaPointX" real NOT NULL, > "CauseAreaPointY" real NOT NULL, > "Direction" TEXT > ); > INSERT INTO "main".sqlite_sequence (name, seq) VALUES ("Disruptions", > '184626834'); > > -- ---------------------------- > -- Indexes structure for table Disruptions > -- ---------------------------- > CREATE INDEX "Disruptions_Idx1" ON Disruptions ("location" COLLATE > NOCASE ASC, "corridor" COLLATE NOCASE ASC, "status" COLLATE NOCASE ASC, > "category" COLLATE NOCASE ASC, "severity" COLLATE NOCASE ASC, > "levelOfInterest" COLLATE NOCASE ASC, "version" COLLATE NOCASE ASC, > "subCategory" COLLATE NOCASE ASC, "startTime" COLLATE NOCASE ASC); > CREATE UNIQUE INDEX "Disruptions_Idx2" ON Disruptions ("Disruption_id" > COLLATE NOCASE ASC, "version" COLLATE NOCASE ASC, "category" COLLATE > NOCASE ASC, "subCategory" COLLATE NOCASE ASC); > CREATE UNIQUE INDEX "Disruptions_Idx3" ON Disruptions ("version" COLLATE > NOCASE ASC, "Disruption_id" COLLATE NOCASE ASC, "location" COLLATE > NOCASE ASC); > CREATE INDEX "Disruptions_idx4" ON Disruptions ("status" COLLATE NOCASE > ASC); > > PRAGMA foreign_keys = true; > ``` > > As part of the larger more complex query, we are executing the query > > ``` > select * from Disruptions where status = 2 OR status = 6; > ``` > > Status is one of six values, 1 to 6 and is probably not evenly > distributed across the 180M rows. > > If we do > > ``` > sqlite> explain query plan select * from Disruptions where status = 2 OR > status = 6; > selectid|order|from|detail > 0|0|0|SCAN TABLE Disruptions > ``` > > We can see that table scanning a 180M records is going to be slow, no > matter what the rest of the complex query is like. > > We have an index Disruptions_idx4 which we *think* should speed it up, > but the query plan doesn't seem to take this into account. > > We think that only having six values of Status means that the speed up > from the index is not going to be fantastic but every little helps. > > We have run analyze on the database and that hasn't helped. > > Our gut feeling at this moment is that we have the design structure > wrong in our DB and we're going to have to take a long hard look at what > we're doing, do a redesign and a rebuild as we simply got it wrong from > the beginning. Hindsight is wonderful ;) In the interim (as this is a > 2-3 month job), we need to speed our query up from 90 mins down to > something in the tens of mins. > > Any suggestions very much welcomed, > > Thanks > > Rob > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
