List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:June 17 1999 5:39pm
Subject:Re: Problem with mysql-optimization ? Possible bug
View as plain text  
Hermann-Marcus Behrens wrote:
> 
> Hello!
> 
> The following sql-command:
> 
> EXPLAIN SELECT Adresse.Name, Party.id, WplanParty.id
> FROM Adresse, Party, WplanParty
> WHERE WplanParty.fiParty = Party.id AND Adresse.id = Party.fiAdresse AND
> WplanParty.dateAnfang <= '1999-08-16'
> 
> produces this output:
> 
> table      type   possible_keys                key     key_len ref
> rows Extra
> ---------- ------ ---------------------------- ------- ------- -------------
> ----- ---- ----------
> WplanParty ALL    dateAnfang,fiParty,datumpaar NULL    NULL    NULL
> 988  where used
> Party      eq_ref PRIMARY,fiAdresse            PRIMARY 4
> WplanParty.fiParty 1    NULL
> Adresse    eq_ref PRIMARY                      PRIMARY 4
> Party.fiAdresse    1    NULL
> 
> 3 Row(s) affected
> 
> If i change the last line in the sql-command to :
> WplanParty.dateAnfang > '1999-08-16'
> 
> the output resulst in:
> table      type   possible_keys                key       key_len ref
> rows Extra
> ---------- ------ ---------------------------- --------- ------- -----------
> ------- ---- -----
> WplanParty range  dateAnfang,fiParty,datumpaar datumpaar NULL    NULL
> 34   NULL
> Party      eq_ref PRIMARY,fiAdresse            PRIMARY   4
> WplanParty.fiParty 1    NULL
> Adresse    eq_ref PRIMARY                      PRIMARY   4
> Party.fiAdresse    1    NULL
> 
> In the last case, mysql will use an Index for the selection, in the first
> case, it uses many more rows AND in the key-column there is a NULL. Did i
> make a mistake or is this a mysql-bug ?
> 
> ciao, Hermi
> 
> PS:
> SHOW KEYS FROM WplanParty
> Table      Non_unique Key_name     Seq_in_index Column_name  Collation
> Cardinality Sub_part
> ---------- ---------- ------------ ------------ ------------ --------- -----
> ------ --------
> WplanParty 0          PRIMARY      1            id           A         988
> NULL
> WplanParty 1          dateAnfang   1            dateAnfang   A         NULL
> NULL
> WplanParty 1          dateEnde     1            dateEnde     A         NULL
> NULL
> WplanParty 1          pm           1            pm           A         NULL
> NULL
> WplanParty 1          mo           1            mo           A         NULL
> NULL
> WplanParty 1          di           1            di           A         NULL
> NULL
> WplanParty 1          mi           1            mi           A         NULL
> NULL
> WplanParty 1          do           1            do           A         NULL
> NULL
> WplanParty 1          fr           1            fr           A         NULL
> NULL
> WplanParty 1          sa           1            sa           A         NULL
> NULL
> WplanParty 1          so           1            so           A         NULL
> NULL
> WplanParty 1          fiParty      1            fiParty      A         NULL
> NULL
> WplanParty 1          fiEinstufung 1            fiEinstufung A         NULL
> NULL
> WplanParty 1          datumpaar    1            dateAnfang   A         NULL
> NULL
> WplanParty 1          datumpaar    2            dateEnde     A         NULL
> NULL
> 
> 15 Row(s) affected
> 
> SHOW COLUMNS FROM WplanParty
> Field        Type          Null Key Default             Extra
> ------------ ------------- ---- --- ------------------- --------------
> id           int(11)       NULL PRI 0                   auto_increment
> pm           tinyint(4)    NULL MUL 0                   NULL
> mo           tinyint(4)    NULL MUL 0                   NULL
> di           tinyint(4)    NULL MUL 0                   NULL
> mi           tinyint(4)    NULL MUL 0                   NULL
> do           tinyint(4)    NULL MUL 0                   NULL
> fr           tinyint(4)    NULL MUL 0                   NULL
> sa           tinyint(4)    NULL MUL 0                   NULL
> so           tinyint(4)    NULL MUL 0                   NULL
> dateAnfang   datetime      NULL MUL 0000-00-00 00:00:00 NULL
> dateEnde     datetime      NULL MUL 0000-00-00 00:00:00 NULL
> fiParty      int(11)       NULL MUL 0                   NULL
> uhrzeit      datetime      NULL NUL 0000-00-00 00:00:00 NULL
> timestamp    timestamp(14) YES  NUL NULL                NULL
> Beschreibung text          YES  NUL NULL                NULL
> fiEinstufung int(11)       NULL MUL 0                   NULL
> Information  text          YES  NUL NULL                NULL
> 
> 17 Row(s) affected

Hi Hermann-Marcus

There is no bug and you didn't make any mistake :)

In the first query the result of WplanParty will have 954 rows out of 988. 
The optimizer is only using key's, if they reduce the amount of rows selected to 1/3 of
the whole table size.
For WplanParty this means, the resulting row number must be less than 329.
In the second query this condition is met (34 rows only) and so the key is used.

The optimizer assumes the resulting rowcount from infos stored in the keys.

Tschau
Christian

Thread
Problem with mysql-optimization ? Possible bugHermann-Marcus Behrens17 Jun
  • Re: Problem with mysql-optimization ? Possible bugChristian Mack17 Jun