Re: Query optimization (what am I doing wrong?)

[Martin Edelius]

I'll look into this approach. Thanks!


Best regards,
Martin Edelius

Spirex Digital Design
--------------------------------
www: http://www.spirex.se
Mail: martin.edelius@stripped
Phone: [phone]
Fax: [phone]
Aröds Industriväg 3c
S-422 43 Hisings Backa
--------------------------------
If I haven't got back to you or done what I'm supposed to, let me know again
as I have too much to do for my own good...

----- Original Message -----
From: "Tonu Samuel" <tonu@stripped>
To: "Martin Edelius" <martin.edelius@stripped>
Cc: "MySQL mailing list" <mysql@stripped>
Sent: Wednesday, March 01, 2000 4:10 PM
Subject: Re: Query optimization (what am I doing wrong?)


> Martin Edelius wrote:
>
> > I read that I should try and avoid OR clauses but I really don't know
how to
> > do that in this query.
>
> Ok. Bad is that I can't understand swedish language and your database
> contexts. But seems that you have something wrong with you database
> structure. Maybe it's more understandable if I do some "bad" example and
> "good" example and you get the point (if this applies in context at
> all).
>
> Like you did:
>
> CREATE TABLE apple (
>   id int unsigned not null auto_increment primary key,
>   colour varchar(40) not null,
>   weight int unsigned
> )
>
> CREATE TABLE orange (
>   id int unsigned not null auto_increment primary key,
>   colour varchar(40) not null
> )
>
> CREATE TABLE fruits {
>   id int unsigned not null auto_increment primary key,
>   fruit_id int unsigned
> }
>
> And you are doing
>
>  colour="red")
>
> Much better is reorganize tables:
>
> CREATE TABLE apple (
>   id int unsigned not null auto_increment primary key,
>   weight int unsigned
> )
>
> CREATE TABLE orange (
>   id int unsigned not null auto_increment primary key,
> )
>
> CREATE TABLE fruits {
>   id int unsigned not null auto_increment primary key,
>   fruit_type enum("orange","apple") not null,
>   fruit_id int unsigned
> }
>
> And do
>
> SELECT something FROM fruits,orange,apple WHERE (apple.id=fruit_id OR
> orange.id=fruit_id) AND colour="green".
>
> Try to use same idea on your tables. You are duplicating varchar fields
> and this is bad. Integers run always much faster even without there
> OR-s.
>
> Maybe this description is too short. Please ask more if you need.
>
> --
> +----------------------------------------------------------------+
> | TcX  ____  __     _____   _____  ___                           |
> |     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|     Tõnu Samuel           |
> |    /*/ /*/ /*/   \*\_   |*|   |*||*|     tonu@stripped        |
> |   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|     Tallinn, Estonia      |
> |  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|____                       |
> |  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                      |
> |             /*/             \*\                Developers Team |
> +----------------------------------------------------------------+
>