MySQL Bugs: #19553: SQL_CALC_FOUND_ROWS is much slower than COUNT(*)

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

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.

Could you add SHOW CREATE TABLE and EXPLAIN output for the actual table and queries being used?

Could you add SHOW CREATE TABLE and EXPLAIN output for the actual table and queries being used?

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)

Looks like a perfect duplicate of know feature request, bug #18454. Work in progress.

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.

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.