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").
2) I don't know if this will help, but try moving the
category.name = '" + category + "'"
term to the front of the WHERE clause.
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
HTH
Ulrik P.
--
Ulrik Petersen, PhD student, MA, B.Sc.
Aalborg University, Denmark
