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] -----------------------------------------------------------------------------