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
-- note DEFAULT keyword
time DEFAULT CURRENT_TIMESTAMP
db eval {CREATE TABLE data(\
data_id INTEGER PRIMARY_KEY, \
position_id INTEGER, \
x INTEGER, \
y REAL);}
Same here - it's PRIMARY KEY with no underscore.
#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;
And for that to work efficiently, you want another index on
data.position_id
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
