On Sat, Jan 04, 2003 at 03:25:00PM +0200, Victoria Reznichenko wrote:
> On Saturday 04 January 2003 14:53, harm wrote:
>
> > > 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.
>
> I tested it on 4.0.7 and MySQL picks up right index.
> What is your table structure?
| nr | int(10) unsigned | | PRI | NULL | auto_increment |
| weg | enum('n','y') | | MUL | n | |
| dbase | varchar(15) | | | | |
| land | enum('nl','be') | | | nl | |
There are about 35 other fields so I won`t include them all.
The table has about 1.5 mil rows. Type is innodb.
Query:
describe select nr from ad where dbase='auto_access' and weg="n" and land="nl" order
by ad.nr;
Describe:
| table | type | possible_keys | key | key_len | ref | rows |
|Extra |
| ad | ref | bla4,weg,weg_3,weg_4,weg_5,weg_2 | weg_5 | 17 |
|const,const,const | 4028 | Using where; Using index; Using filesort |
Matches about 1873 rows. (Varies, from 10 to 5000)
Indexes:
| ad | 1 | weg_5 | 1 | weg | A
| 20 | NULL | NULL | | BTREE | |
| ad | 1 | weg_5 | 2 | land | A
| 20 | NULL | NULL | | BTREE | |
| ad | 1 | weg_5 | 3 | dbase | A
| 20 | NULL | NULL | | BTREE | |
| ad | 1 | weg_2 | 1 | weg | A
| 20 | NULL | NULL | | BTREE | |
| ad | 1 | weg_2 | 2 | land | A
| 20 | NULL | NULL | | BTREE | |
| ad | 1 | weg_2 | 3 | dbase | A
| 890 | NULL | NULL | | BTREE | |
| ad | 1 | weg_2 | 4 | nr | A
| 1729450 | NULL | NULL | | BTREE | |
It uses 'weg_5', it should use 'weg_2'.
Thanks for the help,
Harmen
--
The Moon is Waxing Crescent (4% of Full)
---------------------------------------------------------------------
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
