On Tue, 12 Nov 2002 10:24:34 +0800 Jaime Teng <[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? > > > > Hi, > > 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? > > Does this mean, that there is absolutely no way to improve > my query? Not sure, I'm no pro on all the ways to optimize queries, maybe someone other on the list has some more ideas? > > Hmm.. > > I read lots of good stuffs with MySQL 4.x; is this already > safe to use? I'm using it in production on myphorum.de for some months already, works without a hitch so far. Thomas > > regards, > jaime > > > > > > >Thomas > > > >On Mon, 11 Nov 2002 16:41:50 +0800 Jaime Teng <[EMAIL PROTECTED]> wrote: > > > >> Hi, > >> > >> I have a mysql table: > >> > >> mysql> describe eventlog; > >> +-------------+------------------+ > >> | Field | Type | > >> +-------------+------------------+ > >> | id | int(10) unsigned | > >> | time | datetime | > >> | source | varchar(10) | > >> | description | varchar(255) | > >> +-------------+------------------+ > >> > >> id is a unique auto_increment key. the way data is entered, this > >> key is always sorted. and most importantly, it is a KEY index too. > >> > >> Currently, this table has about 400,000 entries occupying about > >> 30MB of hard disk space. > >> > >> 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? > >> > >> > >> jaime > >> > >> > >> > >> > >> > >> --------------------------------------------------------------------- > >> 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 > >> > > > >--------------------------------------------------------------------- > >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 > > > >. > > > > > --------------------------------------------------------------------- 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
