Hi.
On Tue 2002-11-12 at 10:24:34 +0800, [EMAIL PROTECTED] wrote:
> At 04:47 PM 11/11/2002 +0800, Thomas Seifert wrote:
> >Are you using MySQL-3.23.x?
> >AFAIK it doesn't use a key for ORDER BY (MySQL-4.x does).
> >Maybe thats the cause?
> >
>
> Yes. Im using 3.23.52(nt). I just upgraded it from 3.23.27.
>
> You mean, if I have ORDER BY as part of the query statement,
> MySQL doesnot use index/keys?
That is not completely correct. MySQL does use indexes with ORDER BY,
just not in all cases. One of these cases is ORDER BY ... DESC. That
is only optimized from the 4.0 version on.
You have a further obstacle, namely asking for almost all values,
which normally uses no indexes. Use of indexes makes sense in your
case, because you limit the result to 20 rows. I have no 4.0 version
here for testing. The 3.23.x version does the right thing for the
ascending case:
SELECT * FROM eventlog WHERE id > number ORDER BY id limit 20
So there is reason to believe that 4.0 does the right thing for the
descending case.
> Does this mean, that there is absolutely no way to improve
> my query?
I had a similar case in which I was able to rewrite the query to ORDER
BY (ASC) and did the sorting on application site. Don't know if that
is possible in your case.
> I read lots of good stuffs with MySQL 4.x; is this already
> safe to use?
You have to judge for yourself. MySQL 4.0 is declared beta since end
of August. From what I read on this list, it is considered quite safe
when you use "old" features (i.e. those which existed in 3.23.x
already). If you use features new in 4.0 you should do reasonable
testing before using them on a production machine.
Regards,
Benjamin.
[...]
> >> Whenever I try to perform:
> >> "SELECT * FROM eventlog where id < number ORDER BY id DESC limit 20;"
> >>
> >> The result is very slow, taking 5~10 seconds WHEN number is almost
> >> at the very top of the list:
> >> example,
> >> if max(id) is 3000000, then doing the above search with number being
> >> 2999990, will be very slow. the performance ONLY increases when
> >> number is very low OR when
> >> "select count(*) from eventlog where id < number;" would give a
> >> small number.
> >>
> >> Is there a way to increase the performance of my table/search?
[...]
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
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
