Anyone know how the index chooser works, and why it is often so bad?
For example, with the following query:
SELECT lid,brief,title FROM poems left join poets on poems.mid =
poets.mid WHERE poems.mid =9365 ORDER BY created DESC LIMIT 0,10
With an explain of:
*************************** 1. row ***************************
table: poems
type: ref
possible_keys: mid,mid_2
key: mid_2
key_len: 3
ref: const
rows: 17
Extra: Using where; Using filesort
*************************** 2. row ***************************
table: poets
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: poems.mid
rows: 1
Extra:
2 rows in set (0.00 sec)
The index 'mid' is on (mid, created), while mid_2 is on
(mid,type,created).
It should be obvious in this case that 'type' field is not used while
all the elements of the first one are, so it can avoid the filesort by
using mid.
I find myself adding complex sections to the dynamic search portion of
my site for the query-creation code to add 'use index(blah)' in many
different cases, as the optimizer isn't getting it right...
The explain for the query with a 'use index(mid)' is:
*************************** 1. row ***************************
table: poems
type: ref
possible_keys: mid
key: mid
key_len: 3
ref: const
rows: 26
Extra: Using where
*************************** 2. row ***************************
table: poets
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: poems.mid
rows: 1
Extra:
2 rows in set (0.00 sec)
The obvious answer to my question would be that it choses the index that
returns the least number of rows to be searched. But why would the
indicies return different numbers in this case, when they're both only
using the (mid) part of the field???
I suspect it uses some algorithm to 'guess' the number of rows, and this
usually gives a lower number to bigger indicies?
Thanks,
Kevin Watt
Community Manager, Allpoetry.com
What happened to the cow who went for a drive? He got a Moo_ving
violation
What do you call someone who is crazy about hot chocolate? A cocoa nut
What do bees use to cut wood? Buzz saws
Who eats at underwater resturants ? Suba diners
How do really small people call each other ? On Microphones
How do you fix a broken chimp? With a monkey wrench
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
