Re: Slow LIMIT Query
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 18,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 >= 18 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 18, 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]
Re: Slow LIMIT Query
On 05/06/2005, "Doug V" wrote: > In your followup message [from [EMAIL PROTECTED], 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? I find similar behaviour with one of my standard testtables: CREATE TABLE dtfoo ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, dt DATETIME, d VARCHAR(100), KEY (dt) ) ENGINE = MyISAM; which is filled with 25 records with random dt columns BETWEEN '2000-01-01 00:00:00' AND '2005-12-31 23:59:59', and in which the d column just contains a character copy of dt (to have *some* other data ;-). Essentially: LIMIT clauses from the start of the SELECT are extremely fast, while LIMIT clauses from the end of the SELECT are extremely slow (even more so when the result set includes not only the id but also the the dt column and/or the d column.) - we're talking factors > 1000 between fast and slow when both dt and d are included in the result set. I guess this is because the index on dt can be used to *locate* a record, (as in 'WHERE dt > @some_datetime'), but *not* to count how many records come before a certain @some_datetime, wihch is needed for a LIMIT clause. By the way, if you don't want the reverse ordering from SELECT id FROM dtfoo ORDER BY dt DESC LIMIT 0, 10; you can use a subquery (if your on MySQL 4.1.x): SELECT dtfoo2.id FROM (SELECT id, dt FROM dtfoo ORDER BY dt DESC LIMIT 0, 10) AS dtfoo2 ORDER BY dtfoo2.dt ASC; which is still very fast. Personally, I never feel comfortable with LIMIT n,m clauses where n is high (perhaps maybe for implementing pagination on web pages). I always prefer to set my 'start' in the WHERE clause, so the index can do its work, e.g: SELECT id, dt FROM dtfoo WHERE dt > @some_datetime ORDER BY dt ASC LIMIT 0, 10; -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow LIMIT Query
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 18, 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]
Re: Slow LIMIT Query
But i give you a suggestion (theoritical) : if data are reverse sorted , LIMIT 18, 10 will be LIMIt 2, 10 who knows ? Mathias Selon [EMAIL PROTECTED]: > Hi, > i and i think all people will think the same : i can't help. why ? > > have you described your tables ? > have your given the size of each table ? > have you list the indexes ? > have you specify the storage type ? > and > have you given the query ? > > if you're looking for just a theoritical response, docs.mysql.com can give > it. > > and sorry, this is not an ofense . > > Mathias > > > Selon Doug V <[EMAIL PROTECTED]>: > > > When I do a SELECT using STRAIGHT JOIN against multiple tables where the > > main table has about 200k rows, it is very fast retrieving the latest rows, > > ie "LIMIT 0, 10", but extremely slow retrieving older rows, for example, > > "LIMIT 18 , 10". Doing an EXPLAIN shows that no filesort or temporary > > table is being used. When I do the SELECT without the STRAIGHT JOIN, it > does > > do a filesort and is a little bit faster. Is there anyway to speed such a > > query up? 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow LIMIT Query
Hi, i and i think all people will think the same : i can't help. why ? have you described your tables ? have your given the size of each table ? have you list the indexes ? have you specify the storage type ? and have you given the query ? if you're looking for just a theoritical response, docs.mysql.com can give it. and sorry, this is not an ofense . Mathias Selon Doug V <[EMAIL PROTECTED]>: > When I do a SELECT using STRAIGHT JOIN against multiple tables where the > main table has about 200k rows, it is very fast retrieving the latest rows, > ie "LIMIT 0, 10", but extremely slow retrieving older rows, for example, > "LIMIT 18 , 10". Doing an EXPLAIN shows that no filesort or temporary > table is being used. When I do the SELECT without the STRAIGHT JOIN, it does > do a filesort and is a little bit faster. Is there anyway to speed such a > query up? 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]
Slow LIMIT Query
When I do a SELECT using STRAIGHT JOIN against multiple tables where the main table has about 200k rows, it is very fast retrieving the latest rows, ie "LIMIT 0, 10", but extremely slow retrieving older rows, for example, "LIMIT 18 , 10". Doing an EXPLAIN shows that no filesort or temporary table is being used. When I do the SELECT without the STRAIGHT JOIN, it does do a filesort and is a little bit faster. Is there anyway to speed such a query up? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
