Hi Doug, with a desc index on stuffed_date, an optimiezd table, the query runs in : mysql> select * from stuff order by stuffed_date desc limit 180000,10; +-------+--------------+ | id | stuffed_date | +-------+--------------+ | 88233 | 2005-07-08 | | 88228 | 2005-07-08 | | 88218 | 2005-07-08 | | 88198 | 2005-07-08 | | 88153 | 2005-07-08 | | 88148 | 2005-07-08 | | 88138 | 2005-07-08 | | 88118 | 2005-07-08 | | 88078 | 2005-07-08 | | 87993 | 2005-07-08 | +-------+--------------+ 10 rows in set (0.17 sec)
This is not 0s, buti don't think you can have it. A workaroud should be an auto_increment with no gap, then a select ... from stuff where id >= 180000 limit 10, hoping an index rang scan, for a covering index. Mathias Selon Doug V <[EMAIL PROTECTED]>: > Hi, > > I have tried to simply the problem and it exists without any JOINs. > > >have you given the query ? > > SELECT id FROM stuff ORDER BY stuffed_date DESC LIMIT 180000, 10 -> .43 sec > > SELECT id FROM stuff ORDER BY stuffed_date DESC LIMIT 0, 10 -> .0007 sec > > >have you described your tables ? > > stuffed_date is INDEXed > > >have your given the size of each table ? > > The table is about 200k rows. > > >have you list the indexes ? > > stuff table has several indices, including 'id' and 'stuffed_date'. > > >have you specify the storage type ? > > MYISAM > > In your followup message, you mention reverse sorting the query. I imagine > on the application side I would need to reverse sort again to get the > correct order. Are there any other ways to speed up such a query? Thanks. > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
