[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Newbie sql: query and joining more than two tables
From:       Karim Ryde <karim () bredband ! net>
Date:       2005-06-06 10:02:30
Message-ID: 200506061202.30233.karim () bredband ! net
[Download RAW message or body]

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.

[prev in list] [next in list] [prev in thread] [next in thread]