@Scott: Id is a primary key. I believe PKs always have indexes. @Griggs: While the database engine is to be run on a full blown PC, I have three needs that have lead me to choose SQLite:
a) It should be completely integrated/embedded within the application; no separate install. Just a single .DLL ;-) b) It must have bindings with .Net 2.0 and Mono >1.2.5. c) Open-Source. Switching to a client-server application like PostgreSQL that can't be embedded into the application would actually be my last resort. Cheers and thanks! Hugo Ferreira On Dec 31, 2007 11:56 PM, Scott Baker <[EMAIL PROTECTED]> wrote: > Hugo Ferreira wrote: > > Hi everyone, > > > > I seem to be having a problem here with LEFT JOINS between tables and > > results of INNER JOINS. Take for example the following example (table > > definition is in the end): > > > > TABLE COUNT esparqueologico: 750 > > TABLE COUNT data: 3828 > > TABLE COUNT reftemporal: 3972 > > > > This query would take 6.7s to run (750 rows): > > > > select * from esparqueologico oe left join > > (data d cross join reftemporal r on d.reftemporal_id = r.id) x > > on oe.datacao_id = x.id > > > > However this takes virtually zero time (750 rows): > > > > select * from esparqueologico oe left join data d on oe.datacao_id = > d.id > > > > And this takes 0.1s (3828 rows): > > > > select * from data d inner join reftemporal r on d.reftemporal_id = r.id > > > > It seems to me that indexes are lost in the first query. Here is the > data > > definition I'm using: > > > > CREATE TABLE data ( > > id guid NOT NULL, > > reftemporal_id guid NOT NULL, > > PRIMARY KEY (id), > > FOREIGN KEY (reftemporal_id) REFERENCES reftemporal(id), > > ) > > > > CREATE TABLE reftemporal ( > > id guid NOT NULL, > > subtype varchar, > > PRIMARY KEY (id) > > ) > > > > CREATE TABLE esparqueologico ( > > id guid NOT NULL, > > datacao_id guid, > > PRIMARY KEY (id), > > FOREIGN KEY (datacao_id) REFERENCES data(id), > > ) > > > > CREATE INDEX idx_data_reftemporal_id ON data(reftemporal_id) > > CREATE INDEX idx_esparqueologico_datacao_id ON > esparqueologico(datacao_id) > > I'm not an expert, but don't you want an index on reftemporal.id as > well? You're querying it in your JOIN clause, but there's no index > on the field. > > -- > Scott Baker - Canby Telcom > RHCE - System Administrator - 503.266.8253 > > > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > > ----------------------------------------------------------------------------- > > -- スプーンが ない
