Thanks for your suggestions! On Monday 06 June 2005 05.22, Ulrik Petersen wrote: > Hi Karim, > > Cláudio Leopoldino wrote: > > You may use EXPLAIN clause and verify the reazon... > > > > Cláudio > > > >> Hi! > >> > >> I hope to get some feedback whether the query time is what I should > >> expect. > >> Running this query below takes several seconds - typically 1-3s. > >> > >> SELECT package.id, package.name, package.description, > >> package.size, package.latest, version.version > >> FROM category, package, version > >> WHERE package.idCategory = category.id > >> AND category.name = '" + category + "'" > >> AND version.idPackage = package.id " > >> ORDER BY lower( package.name ); > >> > >> The three tables are like this: > >> CREATE TABLE category ( id INTEGER UNIQUE, > >> name VARCHAR(32) ); > >> CREATE INDEX index_name ON category ( name ); > >> > >> CREATE TABLE package ( id INTEGER UNIQUE, > >> idCategory INTEGER, > >> name VARCHAR(32), > >> latest VARCHAR(32), > >> description > >> VARCHAR(255), > >> size VARCHAR(32), > >> keyword VARCHAR(32)); > >> CREATE INDEX index_name ON package ( name ); > >> > >> CREATE TABLE version ( id INTEGER UNIQUE, > >> idPackage INTEGER, > >> version VARCHAR(32), > >> date VARCHAR(32)); > >> > >> The table category has 136 rows, package 9379 rows and version 19369 > >> rows. > >> > >> Regards, > >> /Karim > > A couple of points: > > 1) You may wish to say "INTEGER PRIMARY KEY" rather than "INTEGER > UNIQUE". The reason can be read here: > > http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuning > > (search for "INTEGER PRIMARY KEY").
First I populate a temporary table with has "id INTEGER PRIMARY KEY" then move all rows to this table. Also by using INTEGER UNIQUE it is automatically indexed. http://www.sqlite.org/lang_createtable.html says: "... The UNIQUE constraint causes an index to be created on the specified columns. ..." > > 2) I don't know if this will help, but try moving the > > category.name = '" + category + "'" > > term to the front of the WHERE clause. Gives a slight improvement of ~0.10s. > 3) Have you read Dr. Hipp's slides from PHP2004? > > http://www.sqlite.org/php2004/page-001.html > > On slide 48, it starts talking about how to organize your WHERE clauses > for using indexes: > > http://www.sqlite.org/php2004/page-048.html Yes, I've read the slides. Optimizing joins for two tables is easy and fast - 0.01s. But taking the found rowid and looking up a row on third table is slow... > HTH > > Ulrik P.
