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, \
> > time CURRENT_TIMESTAMP);}
>
> You probably meant
>
> -- note no underscore between PRIMARY and KEY
> position_id INTEGER PRIMARY KEY
Oops! Fixed now. Thanks.
> -- note DEFAULT keyword
> time DEFAULT CURRENT_TIMESTAMP
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?
Cheers,
James.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
