List:General Discussion« Previous MessageNext Message »
From:Martin Edelius Date:March 3 2000 11:36am
Subject:Re: Query optimization (what am I doing wrong?)
View as plain text  
Hi Tonu.

The ideas you mention doesn't work the way we have designed our databases
today. When we redesign the entire application (which we are doing right
now) we will look at an approach like the one you describe.

What I don't understand is why the query isn't optimized at all? What am I
doing wrong?


Best regards,
Martin Edelius

Spirex Digital Design
--------------------------------
www: http://www.spirex.se
Mail: martin.edelius@stripped
Phone: +46-31-514651, 0708-113711
Fax: +46-31-514331
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 |
> +----------------------------------------------------------------+
>

Thread
Query optimization (what am I doing wrong?)Martin Edelius1 Mar
  • Re: Query optimization (what am I doing wrong?)Tonu Samuel1 Mar
  • Re: Query optimization (what am I doing wrong?)Tonu Samuel1 Mar
  • Re: Query optimization (what am I doing wrong?)Tonu Samuel1 Mar
  • Re: Query optimization (what am I doing wrong?)Martin Edelius1 Mar
  • Re: Query optimization (what am I doing wrong?)Martin Edelius3 Mar
  • Re: Query optimization (what am I doing wrong?)Jan Dvorak3 Mar
  • Re: Query optimization (what am I doing wrong?)Martin Edelius3 Mar