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
• Status: Verified
• 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)
• CPU Architecture: Any
• Tags: limit, order by, performance, sql_calc_found_rows

[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;`