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?
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
