Newbie sql: query and joining more than two tables

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Newbie sql: query and joining more than two tables

Bugzilla from karim@bredband.net
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
Reply | Threaded
Open this post in threaded view
|

Re: Newbie sql: query and joining more than two tables

Cláudio Leopoldino

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

++++++++++++++++++++++++++++++++++++++++++++++++++++++++
"Se você nunca abriu mão de algo pelos seus ideais, provavelmente não tem
nenhum ideal."
Cláudio Bezerra Leopoldino
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Profissional: [hidden email]
Acadêmico: [hidden email]
Piadas e Afins: [hidden email]
++++++++++++++++++++++++++++++++++++++++++++++++++++++++


       
       
               
____________________________________________________
Yahoo! Mail, cada vez melhor: agora com 1GB de espa?o gr?tis! http://mail.yahoo.com.br

Reply | Threaded
Open this post in threaded view
|

Re: Newbie sql: query and joining more than two tables

Ulrik Sandborg-Petersen
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


Reply | Threaded
Open this post in threaded view
|

Re: Newbie sql: query and joining more than two tables

Bugzilla from karim@bredband.net
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.
Reply | Threaded
Open this post in threaded view
|

How to create an empty sqlite3 database?

Marco Bambini
In reply to this post by Ulrik Sandborg-Petersen
What is the best way to create an empty sqlite3 database?
I mean without tables and rows inside it.

The problem is that if I create a 0 length file, when I try to open  
it with sqlite3_open I get the error "file is encrypted or is not a  
database".
Maybe a possible solution could be to manually write the SQLITE3  
header into the file, but is it safe?

Thanks,
Marco Bambini
Reply | Threaded
Open this post in threaded view
|

Re: How to create an empty sqlite3 database?

Martin Engelschalk
Hi,

just do an sqlite3_open() on a non-existing file. See
http://www.sqlite.org/capi3ref.html#sqlite3_open

Martin

Marco Bambini schrieb:

> What is the best way to create an empty sqlite3 database?
> I mean without tables and rows inside it.
>
> The problem is that if I create a 0 length file, when I try to open  
> it with sqlite3_open I get the error "file is encrypted or is not a  
> database".
> Maybe a possible solution could be to manually write the SQLITE3  
> header into the file, but is it safe?
>
> Thanks,
> Marco Bambini

Reply | Threaded
Open this post in threaded view
|

Re: How to create an empty sqlite3 database?

Dan Kennedy
In reply to this post by Marco Bambini
I don't seem to get that. Maybe you're using an old version.

dan@linux:~> ls db
ls: db: No such file or directory
dan@linux:~> touch db
dan@linux:~> ls -s db
0 db
dan@linux:~> sqlite3 db
SQLite version 3.2.1
Enter ".help" for instructions
sqlite> select * from sqlite_master;
sqlite> .quit
dan@linux:~> ls -s db
0 db
dan@linux:~> sqlite db
SQLite version 2.8.15
Enter ".help" for instructions
sqlite> select * from sqlite_master;
sqlite> .quit
dan@linux:~> ls -s db
0 db



--- Marco Bambini <[hidden email]> wrote:

> What is the best way to create an empty sqlite3 database?
> I mean without tables and rows inside it.
>
> The problem is that if I create a 0 length file, when I try to open  
> it with sqlite3_open I get the error "file is encrypted or is not a  
> database".
> Maybe a possible solution could be to manually write the SQLITE3  
> header into the file, but is it safe?
>
> Thanks,
> Marco Bambini
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com 
Reply | Threaded
Open this post in threaded view
|

Re: How to create an empty sqlite3 database?

Steve Bryant
In reply to this post by Marco Bambini
It appears that SQLite 3 doesn't
actually do anything until a table
is created. Try this:

sqlite3 db
SQLite version 3.2.1
Enter ".help" for instructions
create table x (y);
drop table x;
.quit

>>> [hidden email] 6/6/2005 9:31:26 AM >>>
I don't seem to get that. Maybe you're using an old version.

dan@linux:~> ls db
ls: db: No such file or directory
dan@linux:~> touch db
dan@linux:~> ls -s db
0 db
dan@linux:~> sqlite3 db
SQLite version 3.2.1
Enter ".help" for instructions
sqlite> select * from sqlite_master;
sqlite> .quit
dan@linux:~> ls -s db
0 db
dan@linux:~> sqlite db
SQLite version 2.8.15
Enter ".help" for instructions
sqlite> select * from sqlite_master;
sqlite> .quit
dan@linux:~> ls -s db
0 db



--- Marco Bambini <[hidden email]> wrote:

> What is the best way to create an empty sqlite3 database?
> I mean without tables and rows inside it.
>
> The problem is that if I create a 0 length file, when I try to open

> it with sqlite3_open I get the error "file is encrypted or is not a

> database".
> Maybe a possible solution could be to manually write the SQLITE3  
> header into the file, but is it safe?
>
> Thanks,
> Marco Bambini
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com