List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 4 2000 6:29pm
Subject:Mysql Looses index when adding order by to the query
View as plain text  
Hi!

>>>>> "Wico" == Wico de Leeuw <wico@stripped> writes:

Wico> Hiya
Wico> I ran into something strange:

Wico> i have this query:

Wico> SELECT SQL_SMALL_RESULT HIGH_PRIORITY STRAIGHT_JOIN COUNT(search.page_id) 
Wico> AS counter, search.page_id FROM word, search WHERE (word.word LIKE 'zorg%') 
Wico> AND search.word_id = word.id GROUP BY search.page_id ORDER BY counter DESC, 
Wico> search.count DESC

Wico> explain says:
Wico> +--------+-------+---------------+---------+---------+---------+------+-------+
Wico> | table  | type  | possible_keys | key     | key_len | ref     | rows | Extra |
Wico> +--------+-------+---------------+---------+---------+---------+------+-------+
Wico> | word   | range | PRIMARY,word  | word    |    NULL | NULL    |  259 |       |
Wico> | search | ref   | PRIMARY       | PRIMARY |       2 | word.id | 6359 |       |
Wico> +--------+-------+---------------+---------+---------+---------+------+----- 
Wico> --+'


Wico> when i do this query:

Wico> SELECT SQL_SMALL_RESULT HIGH_PRIORITY STRAIGHT_JOIN COUNT(search.page_id) 
Wico> AS counter, search.page_id FROM word, search WHERE (word.word LIKE 'zorg%') 
Wico> AND search.word_id = word.id GROUP BY search.page_id ORDER BY counter DESC

Wico> explain says:

Wico> +--------+-------+---------------+---------+---------+---------+------+----- 
Wico> --------+
Wico> | table  | type  | possible_keys | key     | key_len | ref     | rows | 
Wico> Extra       |
Wico> +--------+-------+---------------+---------+---------+---------+------+----- 
Wico> --------+
Wico> | word   | range | PRIMARY,word  | word    |    NULL | NULL    |  259 
Wico> |             |
Wico> | search | ref   | PRIMARY       | PRIMARY |       2 | word.id | 6359 | 
Wico> Using index |
Wico> +--------+-------+---------------+---------+---------+---------+------+----- 
Wico> --------+

This is probably correct.  'Using index' can only be used if all
used columns in the table is part of an index.  My guess is that you
don't have an index that has the following index parts:

(search.page_id,search,count)

(You can check this with 'show keys from search')

Regards,
Monty
Thread
Mysql Looses index when adding order by to the queryWico de Leeuw3 Mar
  • Re: Mysql Looses index when adding order by to the querysinisa3 Mar
    • Re: Mysql Looses index when adding order by to the queryWico de Leeuw3 Mar
  • Re: Mysql Looses index when adding order by to the querysasha3 Mar
    • Re: Mysql Looses index when adding order by to the queryWico de Leeuw3 Mar
  • Re: Mysql Looses index when adding order by to the querysasha3 Mar
    • Re: Mysql Looses index when adding order by to the queryWico de Leeuw3 Mar
    • Re: Mysql Looses index when adding order by to the queryWico de Leeuw3 Mar
      • Re: Mysql Looses index when adding order by to the queryWico de Leeuw7 Mar
        • Re: Mysql Looses index when adding order by to the queryBenjamin Pflugmann8 Mar
          • Re: Mysql Looses index when adding order by to the queryWico de Leeuw8 Mar
            • Re: Mysql Looses index when adding order by to the query[SOLVED]Wico de Leeuw8 Mar
  • Mysql Looses index when adding order by to the queryMichael Widenius4 Mar