Bug #46648 SQL_CALC_FOUND_ROWS kills performance with ORDER BY and LIMIT
Submitted: 11 Aug 2009 11:41 Modified: 17 Aug 2009 13:36
Reporter: Jille Timmermans Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1.32, 4.1, 5.0, 5.1, azalea bzr OS:Linux (2.6.28.7)
Assigned to: CPU Architecture:Any
Tags: limit, order by, performance, sql_calc_found_rows
Triage: Triaged: D5 (Feature request)

[11 Aug 2009 11:41] Jille Timmermans
Description:
events_200931 contains 7,118,515 rows and is 900MB and packed; Primary key: id
(I did an ALTER TABLE events_200931 ORDER BY id; but I don't know whether that is of any effect)

This is my initial query:
> SELECT SQL_CALC_FOUND_ROWS * FROM events_200931 ORDER BY id DESC LIMIT 25;
...
25 rows in set (19.00 sec)

If I strip off the SQL_CALC_FOUND_ROWS part:
> SELECT * FROM events_200931 ORDER BY id DESC LIMIT 25;
25 rows in set (0.00 sec)

Or the ORDER BY part:
> SELECT SQL_CALC_FOUND_ROWS * FROM events_200931 LIMIT 25;
25 rows in set (0.00 sec)

The huge performance difference seems weird to me; there is no WHERE clause; so SQL_CALC_FOUND_ROWS could just use the number of rows in the table

How to repeat:
Create a table with a lot of rows and try the above queries.
[11 Aug 2009 14:09] Peter Laursen
Is it an InnoDB table?

Peter
(not a MySQL person)
[11 Aug 2009 14:51] Jille Timmermans
No, MyISAM.
[12 Aug 2009 6:20] Sveta Smirnova
Thank you for the report.

Verified as described using attached test case.

Results:

select now();
now()
2009-08-12 09:11:44
select sql_calc_found_rows * from t1 order by j limit 1;
i       j       empty_string    bool    d
1       2               Y       2002-03-03
select now();
now()
2009-08-12 09:11:59
select sql_calc_found_rows * from t1 order by j limit 1;
i       j       empty_string    bool    d
1       2               Y       2002-03-03
select now();
now()
2009-08-12 09:12:15
select sql_calc_found_rows * from t1 limit 1;
i       j       empty_string    bool    d
1       2               Y       2002-03-03
select now();
now()
2009-08-12 09:12:15
select * from t1 order by j limit 1;
i       j       empty_string    bool    d
1       2               Y       2002-03-03
select now();
now()
2009-08-12 09:12:23
[12 Aug 2009 6:20] Sveta Smirnova
test case for the testsuite

Attachment: bug46648.test (application/octet-stream, text), 1.11 KiB.

[17 Jun 2011 12:49] Arnaud Adant
This bug can cause major performance problems to web applications such a WordPress
when there are more than 10K rows to sort.

see :

http://core.trac.wordpress.org/ticket/10964

The workaround is to split the query into 2.

1. query using order by and limit without SQL_CALC_FOUND_ROWS

2. count(*) from the same query

It improves performance dramatically. The question is : should not the mysql optimizer do the same internally ?
[17 Jun 2011 12:58] Arnaud Adant
This bug is a possible duplicate of this one :

http://bugs.mysql.com/bug.php?id=18454
[8 Jul 2011 19:11] Sveta Smirnova
Bug is still repeatable with current trunk tree (5.6.3). Even worse: it started to run slow with query `select * from t1 order by j limit 1;`