List:General Discussion« Previous MessageNext Message »
From:Matthew Taylor Date:May 7 2002 9:14pm
Subject:Strange query optimization.
View as plain text  
Dose any one know why the query optimizer is doing the following.


explain SELECT citation.citation_id, taxon_name.full_name
FROM taxon_name, taxon_name genus, citation, taxon_name_lookup
WHERE (citation.taxon_name_id = taxon_name.taxon_name_id
AND genus.epithet LIKE 'Pulchr%'
AND taxon_name_lookup.genus_id = genus.taxon_name_id
AND citation.taxon_name_id = taxon_name_lookup.taxon_name_lookup_id)
ORDER BY full_name ASC
 LIMIT 5000

gives the following 

+-------------------+--------+------------------+---------------+---------+----------------------------+---------+----------------+
| table             | type   | possible_keys    | key           |
key_len | ref                        | rows    | Extra          |
+-------------------+--------+------------------+---------------+---------+----------------------------+---------+----------------+
| taxon_name        | ALL    | PRIMARY          | NULL          |   
NULL | NULL                       | 1444913 | Using filesort |
| citation          | ref    | taxon_name_id    | taxon_name_id |     
12 | taxon_name.taxon_name_id   |       1 | Using index    |
| taxon_name_lookup | eq_ref | PRIMARY,genus_id | PRIMARY       |     
12 | citation.taxon_name_id     |       1 |                |
| genus             | eq_ref | PRIMARY,epithet  | PRIMARY       |     
12 | taxon_name_lookup.genus_id |       1 | where used     |
+-------------------+--------+------------------+---------------+---------+----------------------------+---------+----------------+

Which is very bad. It dose a full scan of taxon_name. Change the LIKE
term by just one letter e.g. 

explain SELECT citation.citation_id, taxon_name.full_name
FROM taxon_name, taxon_name genus, citation, taxon_name_lookup
WHERE (citation.taxon_name_id = taxon_name.taxon_name_id
AND genus.epithet LIKE 'Pulche%'
AND taxon_name_lookup.genus_id = genus.taxon_name_id
AND citation.taxon_name_id = taxon_name_lookup.taxon_name_lookup_id)
ORDER BY full_name ASC
 LIMIT 5000

+-------------------+--------+------------------+---------------+---------+----------------------------------------+------+----------------------------------------------------------+
| table             | type   | possible_keys    | key           |
key_len | ref                                    | rows |
Extra                                                    |
+-------------------+--------+------------------+---------------+---------+----------------------------------------+------+----------------------------------------------------------+
| genus             | range  | PRIMARY,epithet  | epithet       |     
32 | NULL                                   | 5040 | where used; Using
index; Using temporary; Using filesort |
| taxon_name_lookup | ref    | PRIMARY,genus_id | genus_id      |     
12 | genus.taxon_name_id                    |   25 | Using
index                                              |
| citation          | ref    | taxon_name_id    | taxon_name_id |     
12 | taxon_name_lookup.taxon_name_lookup_id |    1 | Using
index                                              |
| taxon_name        | eq_ref | PRIMARY          | PRIMARY       |     
12 | citation.taxon_name_id                 |    1
|                                                          |
+-------------------+--------+------------------+---------------+---------+----------------------------------------+------+----------------------------------------------------------+

Much better. 

This only occurs on one machine the live server which is running
4.0.1-alpha on Solaris. I do not get this on a Linux box running 
4.0.1-alpha-Max with the same dataset or on a Linux box running
3.23.41-Max.

Restarting MySQL did not make any difference.

P.S. I am using InnoDB tables.

Matt







Thread
Strange query optimization.Matthew Taylor7 May
Re: Strange query optimization.Heikki Tuuri7 May