Re: [sqlite] Faster way to perform this query?

[Derrell . Lipman]

"Zahraie Ramin-p96152" <[EMAIL PROTECTED]> writes:

> I need to perform the following query:
>
> select a.sourceIndex, a.dsuIP, a.dsuPort, a.sourceType, a.workIp, a.SourceName, 
> b.fileId, b.filePath, b.startTime, b.endTime from dvrpAudioSourceTable a, 
> dvrpAudioFileTable b, dvrpIndexTable c where b.startTime>= 100 and b.endTime <= 200 
> and c.fileId = b.fileId and a.sourceIndex = c.sourceIndex order by a.sourceIndex, 
> b.fileId;
>
> Currently on Sqlite version 2.8.13, this query on tables with 1000 rows
> each, returns 50 rows but it takes about 30 seconds. Is there anyway to
> significantly speed up this query?

Try adding these two indexes and then rerun your query:

    CREATE INDEX b_startTime_endTime_fileId_idx
      ON dvrpAudioFileTable (startTime, endTime, fileId);

    CREATE INDEX c_fileId_sourceIndex_idx
      ON dvrpIndexTable (fileId, sourceIndex);

How much does this speed it up?

Derrell