<cut>
a> Here's the index usage from a current search:
mysql> explain SELECT r.LabelID, r.LabelName, r.URL, r.LocationID, ll.City,
> ll.State_Prov, ll.Country, g.Genre, g.GenreGIF
-> FROM RecordLabel r
-> LEFT JOIN LabelLocation ll ON r.LocationID = ll.LocationID
-> LEFT JOIN GenreCode gc ON r.LabelID=gc.LabelID
-> LEFT JOIN Genre g ON gc.GenreNumber=g.GenreNumber
-> WHERE AlphaOrder LIKE 'c%'
-> ORDER BY r.AlphaOrder, r.LabelID;
a>
> +-------+--------+-------------------------+---------+---------+----------------+------+------------+
a> | table | type | possible_keys | key | key_len | ref |
> rows | Extra |
a>
> +-------+--------+-------------------------+---------+---------+----------------+------+------------+
a> | r | ALL | AlphaOrder,AlphaOrder_2 | NULL | NULL | NULL |
> 257 | where used |
a> | ll | eq_ref | PRIMARY | PRIMARY | 4 | r.LocationID |
> 1 | |
a> | gc | ref | LabelID,PRIMARY | PRIMARY | 4 | r.LabelID |
> 1 | |
a> | g | eq_ref | PRIMARY | PRIMARY | 2 | gc.GenreNumber |
> 1 | |
a>
> +-------+--------+-------------------------+---------+---------+----------------+------+------------+
a> (Using the greater than/less than WHERE clause yields the exact same result.)
Hi!
The above means that MySQL considered using the index of AlphaOrder
for the 'r' table, but concluded that it will be faster to scan the
table than use the index. How many rows it it that matches
AlphaOrder LIKE 'c%' ?
Regards,
Monty
