Matthew,
you could try upgrading to 3.23.49 or .50.
From the changelog:
.....
MySQL/InnoDB-3.23.48, February 9, 2002
* Tuned the SQL optimizer to favor more often index searches over table
scans.
.....
Note that InnoDB-4.0.1 = InnoDB-3.23.47.
Best regards,
Heikki Tuuri
Innobase Oy
---
Order technical MySQL/InnoDB support at https://order.mysql.com/
See http://www.innodb.com for the online manual and latest news on InnoDB
----- Original Message -----
From: "Matthew Taylor" <mtaylor@stripped>
Newsgroups: mailing.database.mysql
Sent: Wednesday, May 08, 2002 12:22 AM
Subject: Strange query optimization.
>
> 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
>
>
>
>
>
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <mysql-thread108277@stripped>
> To unsubscribe, e-mail
<mysql-unsubscribe-mysql=freebsd.csie.nctu.edu.tw@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
