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

[D. Richard Hipp]

Zahraie Ramin-p96152 wrote:
>
> I have the following schema:
>
> 1)Table: dvrpAudioSourceTable
>
> [varchar]        [int]     [int]           [int]              [int]
> [varchar] sourceIndex | dsuIp | dsuPort | sourceType | workIp | SourceName|
>
>
> 2)Table: dvrpAudioFileTable
>
> [int]    [varchar]    [int]          [int]
> fileId | filePath | startTime | endTime
>
> 3)Table: dvrpIndexTable
>
> [varchar]         [int] sourceIndex | fileId
>
> 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;

Try this:

   *  Reorder the FROM clause terms to be B, C, A instead of A, B, C.
   *  Create index on  B.FILEID.
   *  Create index on  A.SOURCEINDEX

--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565