On Tue, Jan 07, 2003 at 12:31:36PM +0100, harm wrote:
> > > Anybody else who has any idea why the index are not used as they should?
> >
> > I've got same things with 3.23.xx and select query through a TCP/IP
> > connection.
> > I don't know why, but you can solve this issue using the USE INDEX syntax
> > for select queries ...
> >
> > http://www.mysql.com/doc/en/SELECT.html
>
> Hmmm, that does help. It does not use the filesort anymore.
>
> I cannot use the 'use index' hardcoed in this query (is is not always the
> fastest).
>
> I think the following is happening:
> A select with only constants and an 'order by' does not use the index
> for the orderby if there are 2 indexes which differ only in that the last
> one has the 'order by' column append to it.
> For example:
> select * from foo where a="const" order by b;
> indexes: first_index (a), second_index (a,b)
>
> The fix should be easy, drop the first_index (It is useless anyway)
> I'll test this hypothesis tonight when I can do the 20 minute taking alter
> table :)
Confirmed, it does use the index correct since I dropped first_index. Seems like a
little bug in the optimiser.
Thanks for the help. The load on the server is notecable lower (about 1.00
down !) now the sort uses the index as well!
Harmen
--
The Moon is Waxing Crescent (49% 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
