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
