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
