On Fri, Jan 03, 2003 at 08:24:17PM -0500, Brian Lindner wrote: > 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
There is an index on 'nr', it is the primary key :) This is one of the examples from the manual which is supposed to use the index for the order by: SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2 Looks like the same situation to me. Thanks, Harmen > > 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) > > > -- The Moon is Waxing Crescent (3% of Full) nieuw.nl - 2dehands.nl: 57873 --------------------------------------------------------------------- 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
