harm, Your order by will not use your weg_2 index because, as you stated, weg_2 index is on (col1, col2, col3, nr) as a group so it wont be used for the individual columns as you need
try to create a separate index on just the nr column, that should be used by the orderby clause. This way your weg_5 will be used for the where (the 3 col) and the new index will be for the orderby Hope that helps -- Brian Lindner Friday, January 3, 2003, 8:05:05 PM, you wrote: > 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) --------------------------------------------------------------------- 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
