> > > #A more complicated query...runs quite slowly. How can
> this be sped
> > > up?
> > > db eval {SELECT position.odo, data.x, data.y from
> position, data WHERE
> > > position.odo BETWEEN 10000 AND 10020;}
> >
> > First, you want an index on position.odo. Second, you don't
> specify any
> > relation between position and data tables, so you generate a full
> > cross-product. You want
> >
> > SELECT position.odo, data.x, data.y
> > FROM position JOIN data ON (position.position_id = data.position_id)
> > WHERE position.odo BETWEEN 10000 AND 10020;
>
> Ah. I have been reading a PHP/MySQL book, that I thought said a MySQL
> server would see the common column names and automagically join the 2.
> Either I misremember what the book said (it's not with me
> here), or this
> is a feature of MySQL, not present in SQLite. Anyway, what
> you suggest
> works just fine.
SQLite does not implement this feature. Its not in the SQL standard AFAIK.
> > And for that to work efficiently, you want another index on
> > data.position_id
>
> I'm guessing that is with;
> CREATE INDEX odo_index ON data (position_id ASC);
Yes.
> If the data is streaming in, and insertions are being made on the fly,
> will an index need to be regenerated periodically, i.e. REINDEX?
No. Indexes are automatically updated.
HTH,
Mike
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
