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
