Bug #19553 SQL_CALC_FOUND_ROWS is much slower than COUNT(*)
Submitted: 5 May 2006 0:18 Modified: 14 May 2006 23:45
Reporter: Wade Bowmer Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0.21, 4.0.* OS:Linux (Linux)
Assigned to: CPU Architecture:Any
Triage: D5 (Feature request)

[5 May 2006 0:18] Wade Bowmer
Description:
Our PHP application has been using SELECT SQL_CALC_FOUND_ROWS * FROM ... LIMIT ... followed by SELECT FOUND_ROWS() for some while on a number of tables to present a pageable interface to the data. The queries use indexed fields to select and sort by, but searching on one particular index returns about a third of the table. This is a common search and the table has over 700000 rows. 

Last night I modified the page code to not include SQL_CALC_FOUND_ROWS and instead use SELECT COUNT(*) to get the total row counts. This is much much faster - on our table about 5 times as fast. 

The table in question is InnoDB. The queries are programatically built to access any of more than a dozen tables, most of them InnoDB. We've seen the problem only on one table with one search because of all the large tables, that's the only search that would return so much data. All queries use WHERE and ORDER BY - a few will also do INNER JOIN. There are no GROUP BYs or HAVING clauses.

How to repeat:
Have a largish table filled with data and indexed on a non-primary field such that one entry returns more than a third of the table. 

SELECT * FROM table WHERE indexed_field = value ORDER BY other_field; should take some tens of seconds.
SELECT * FROM table WHERE index_field = value ORDER BY other_field LIMIT 20; is much quicker.
SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE index_field = value ORDER BY other_field LIMIT 20; is as slow as the first query.
SELECT COUNT(*) FROM table WHERE index_field = value; is faster than any of the above queries.

Sorry, I can't provide any more detail: the only table we can see the effect on is 700,000 rows.
[5 May 2006 10:18] Hartmut Holzgraefe
Could you add SHOW CREATE TABLE and EXPLAIN output for the actual table and queries being used?
[5 May 2006 11:49] Hartmut Holzgraefe
Could you add SHOW CREATE TABLE and EXPLAIN output for the actual table and queries being used?
[7 May 2006 22:51] Wade Bowmer
As requested, the table create:

>CREATE TABLE `tickets` (
  `ticket` char(12) collate utf8_unicode_ci NOT NULL default '0',
  `account` int(10) unsigned NOT NULL default '0',
  `department` int(10) unsigned NOT NULL default '0',
  `subject` char(128) collate utf8_unicode_ci NOT NULL default '',
  `customer` int(10) unsigned NOT NULL default '0',
  `userflag` smallint(5) unsigned NOT NULL default '0',
  `owner` smallint(5) NOT NULL default '0',
  `status` tinyint(1) NOT NULL default '0',
  `substatus` tinyint(1) NOT NULL default '0',
  `priority` tinyint(1) NOT NULL default '2',
  `messages` smallint(5) unsigned NOT NULL default '0',
  `unread` enum('Y','N') collate utf8_unicode_ci NOT NULL default 'Y',
  `notes` enum('Y','N') collate utf8_unicode_ci NOT NULL default 'N',
  `defect` char(32) collate utf8_unicode_ci NOT NULL default '',
  `issue` int(10) unsigned NOT NULL default '0',
  `created` int(11) unsigned NOT NULL default '0',
  `modified` int(11) unsigned NOT NULL default '0',
  `queued` int(10) unsigned NOT NULL default '0',
  `assigned` int(10) unsigned NOT NULL default '0',
  `statuschanged` int(10) unsigned NOT NULL default '0',
  `closed` int(10) unsigned NOT NULL default '0',
  `importance` int(10) unsigned NOT NULL default '0',
  `meta` smallint(5) unsigned NOT NULL default '0',
  `product` smallint(5) unsigned NOT NULL default '0',
  `product_version` char(16) collate utf8_unicode_ci NOT NULL default '',
  `fixed_version` char(16) collate utf8_unicode_ci NOT NULL default '',
  `ticket_code` int(10) unsigned default NULL,
  PRIMARY KEY  (`ticket`),
  KEY `importance` (`importance`),
  KEY `meta` (`meta`),
  KEY `modified` (`modified`),
  KEY `customer` (`customer`),
  KEY `owner_status` (`owner`,`status`),
  KEY `userflag` (`userflag`),
  KEY `product` (`product`),
  KEY `department_unread` (`department`,`unread`,`modified`),
  KEY `ticket_code` (`ticket_code`),
  KEY `owner_state` (`owner`,`unread`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The Explain:
>explain SELECT SQL_CALC_FOUND_ROWS * FROM wgnsupport.tickets WHERE department = '15' ORDER BY unread asc, modified DESC LIMIT 20\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tickets
         type: ref
possible_keys: department_unread
          key: department_unread
      key_len: 4
          ref: const
         rows: 396558
        Extra: Using where; Using filesort
1 row in set (0.00 sec)
[12 May 2006 12:30] Valeriy Kravchuk
Looks like a perfect duplicate of know feature request, bug #18454. Work in progress.
[14 May 2006 23:45] Wade Bowmer
I disagree that it's not a bug, as it definitely 'inconvenient service, but a workaround is available'.

However, since it certainly is undocumented behaviour, I've added a note to the online documentation.
[12 Mar 2008 13:35] sir lord
MySQL Team, I want to ask question about LIMIT:
Is there any plan allowing variables with LIMIT clause?
It's a good feature which MS SQL and other servers don't have, but your LIMIT is limited, please complete this nice job!

.NET Developer.