List:Bugs« Previous MessageNext Message »
From:Wico de Leeuw Date:March 7 2000 1:43pm
Subject:Mysql Loosses indexes when adding ORDER BY to a query
View as plain text  
Hiya

Description:

I have a query which uses a index (acording to explain)
when i do the same query with a orderby added mysql doesn't use the index
(the field that is used to do order by is indexed)

Release MySQL 3.22.32 and 3.22.26a (both from source)

How-To-Repeat:
dump the attached file into a db
and try these query's:

the "good" query: (explain says using index)
SELECT SQL_SMALL_RESULT HIGH_PRIORITY STRAIGHT_JOIN COUNT(search.page_id) 
AS counter, search.page_id FROM word, search WHERE (word.word LIKE 'zorg%') 
AND search.word_id = word.id GROUP BY search.page_id ORDER BY counter DESC

the "bad" query: (no index here :( )
SELECT SQL_SMALL_RESULT HIGH_PRIORITY STRAIGHT_JOIN COUNT(search.page_id) 
AS counter, search.page_id FROM word, search WHERE (word.word LIKE 'zorg%') 
AND search.word_id = word.id GROUP BY search.page_id ORDER BY counter DESC, 
search.count DESC

Greetz,

Wico



Thread
Mysql Loosses indexes when adding ORDER BY to a queryWico de Leeuw7 Mar