Why my SQLite3 query takes time?

SQLite3 query takes a lot of time, how would you do it?

• I have a sqlite3 DB table of around 250 000 rows, my code is written in python. I need to filter it in very specific wat, and it takes much too long time. Table is as follows: self.cur.execute("""create table DetectedVehicles(IdD INTEGER PRIMARY KEY, CLCode INT, DetectionTime INT, PlateNo VARCHAR)""") It's a table for automatic plate number recognition result filtering. And I need to filter it to get (native sql-like statements :) ): Get rows from table DetectedVehicles where vehicles were observed at CLCode="X" before they were observed at CLCode="Y". (implicite: they were observed at both of them) So I need to get list of detectedvehicles, that crossed specific CLCodes in proper sequence, i.e. Y before X. I managed to create something that is working, but it takes about 10seconds for the query. Is there a faster way? The code goes here: self.cur.execute('select distinct PlateNo from DetectedVehicles where CLCode=? intersect select PlateNo from DetectedVehicles where CLCode=?',(CountLocationNo[0],CountLocationNo[1])) PlatesTab=list(self.cur) Results=[] for Plate in PlatesTab: PlateQ1='select * from DetectedVehicles where PlateNo in (?) and ((select DetectionTime from DetectedVehicles where CLCode = ? and PlateNo in (?) ) < (select DetectionTime from DetectedVehicles where CLCode = ? and PlateNo in (?)))' R=list(self.cur.execute(PlateQ1,(Plate,CountLocationNo[0],Plate,CountLocationNo[1],Plate))) if R: TimesOD=self.curST2.execute('select DetectionTime from DetectedVehicles where PlateNo in (?) and (CLCode= ? or CLCode=?)',(Plate,CountLocationNo[0],CountLocationNo[1])).fetchall() if TimesOD: TravelTimes.append(TimesOD[1][0]-TimesOD[0][0]) DetectionTimes.append(TimesOD[0][0]) for i in R: Results.append(i[0]) Results=tuple(Results) QueryCL=' intersect select * from DetectedVehicles where IDd in ' + str(Results) Thanks in advance

• Answer:

  Try: select distinct x.* from DetectedVehicles x join DetectedVehicles y on x.PlateNo = y.PlateNo and x.DetectionTime < y.DetectionTime where x.CLCode=? and y.CLCode=? or: select x.* from DetectedVehicles x where exists (select 1 from DetectedVehicles y where x.PlateNo = y.PlateNo and x.DetectionTime < y.DetectionTime and x.CLCode=? and y.CLCode=?) I would normally expect the latter query to execute more quickly, but it would be worth running both to check.