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 2:18	Modified:	15 May 2006 1:45
Reporter:	Wade Bowmer
Status:	Duplicate
Category:	Server	Severity:	S4 (Feature request)
Version:	5.0.21, 4.0.*	OS:	Linux (Linux)
Assigned to:		Target Version:
Triage:	D5 (Feature request)

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.