Hello, I am trying to get a sort to use an index. The query is very simple:
select nr from mytable where col1='const' and col2='another const' and col3='YA const' order by nr; The explain gives me: +---------+------+----------------------------------+-------+---------+-------------------+------+------------------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------+------+----------------------------------+-------+---------+-------------------+------+------------------------------------------+ | mytable | ref | bla4,weg,weg_3,weg_4,weg_5,weg_2 | weg_5 | 17 | const,const,const | 2840 | Using where; Using index; Using filesort | +---------+------+----------------------------------+-------+---------+-------------------+------+------------------------------------------+ According to the docs (http://www.mysql.com/doc/en/ORDER_BY_optimisation.html) it should use index 'weg_2', which is an index on (col1, col2, col3, nr) but it doesn`t. You are suppost to tell because there is the 'Using filesort' remark (Again according to the docs). And of course the query is quite slow. Index 'weg_5' is (col1, col2, col3). So, to summarise, the select itself is perfectly fine, using indexes as it should, it is just the sort should use the index as wel. Did I misread the docs or is there anything else? Any clues? Thanks for your time, Harmen (Yes I know there are lots of redundant indexes, they are leftovers from tests to figure this out :) (Using 4.0.7, 3.23.50 did the same thing) -- The Moon is Waxing Crescent (2% of Full) (filter bait: sql, etc) --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
