List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:September 19 1999 11:11am
Subject:Re: select doesn't seem to be using index
View as plain text  
<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
Thread
select doesn't seem to be using indexa rancid amoeba18 Sep
  • Re: select doesn't seem to be using indexPaul DuBois18 Sep
  • Re: select doesn't seem to be using indexBenjamin Pflugmann18 Sep
    • Re: select doesn't seem to be using indexMartin Ramsch18 Sep
    • Re: select doesn't seem to be using indexa rancid amoeba18 Sep
      • Re: select doesn't seem to be using indexBenjamin Pflugmann19 Sep
        • Re: select doesn't seem to be using indexa rancid amoeba19 Sep
          • Re: select doesn't seem to be using indexMichael Widenius20 Sep
        • similar question plus ORDER BY (was Re: select doesn't seem to be using index)AAron nAAs20 Sep
      • Re: select doesn't seem to be using indexMichael Widenius19 Sep