On Mon, 2007-11-19 at 18:14 -0500, Igor Tandetnik wrote:
> James Steward <jamessteward-sFbbPxZDHXw0n/[EMAIL PROTECTED]>
> wrote:
> > db eval {CREATE TABLE position(\
> > position_id INTEGER PRIMARY_KEY, \
> > odo INTEGER, \
> You probably meant
> -- note no underscore between PRIMARY and KEY

Oops!  Fixed now.  Thanks.

> -- note DEFAULT keyword

I discovered that just before I saw your reply ;-)

> > #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.

> 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);

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?


To unsubscribe, send email to [EMAIL PROTECTED]

Reply via email to