Bug #36006 Optimizer does table scan for select count(*) w/5.1.22, .23, not 5.0.51, 5.1.11

Description:
The following describes a database running a version of 5.1.22 installed from a FreeBSD 7.0 package.

mysql> select user(), now(), version(), database();
+----------------+---------------------+---------------+------------+
| user()         | now()               | version()     | database() |
+----------------+---------------------+---------------+------------+
| root@localhost | 2008-04-01 19:31:18 | 5.1.22-rc-log | mysql      |
+----------------+---------------------+---------------+------------+
1 row in set (0.00 sec)

Here is how the table was created.

mysql> show create table sancp;
...edited...
| sancp | CREATE TABLE `sancp` (
  `sid` int(10) unsigned NOT NULL,
  `sancpid` bigint(20) unsigned NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `duration` int(10) unsigned NOT NULL,
  `ip_proto` tinyint(3) unsigned NOT NULL,
  `src_ip` int(10) unsigned DEFAULT NULL,
  `src_port` smallint(5) unsigned DEFAULT NULL,
  `dst_ip` int(10) unsigned DEFAULT NULL,
  `dst_port` smallint(5) unsigned DEFAULT NULL,
  `src_pkts` int(10) unsigned NOT NULL,
  `src_bytes` int(10) unsigned NOT NULL,
  `dst_pkts` int(10) unsigned NOT NULL,
  `dst_bytes` int(10) unsigned NOT NULL,
  `src_flags` tinyint(3) unsigned NOT NULL,
  `dst_flags` tinyint(3) unsigned NOT NULL,
  KEY `p_key` (`sid`,`sancpid`),
  KEY `src_ip` (`src_ip`),
  KEY `dst_ip` (`dst_ip`),
  KEY `dst_port` (`dst_port`),
  KEY `src_port` (`src_port`),
  KEY `start_time` (`start_time`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`sancp_SENSOR_20080308`,`sancp_SENSOR_20080309`, ...edited... `sancp_SENSOR_20080401`) | 
... truncated...

The following EXPLAIN shows how the query would be run.

mysql> explain select count(*) from sancp;
+----+-------------+-------+-------+---------------+----------+---------+------+-----------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows      | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+-----------+-------------+
|  1 | SIMPLE      | sancp | index | NULL          | dst_port | 3       | NULL | 259886280 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+-----------+-------------+
1 row in set (0.18 sec)

Executing this query took, on average, 2 hours and 20 minutes!

I tried upgrading to 5.1.23, but had the same problem.

How to repeat:
I got the same results on all databases I tried that were running 5.1.22 or 5.1.23.

Suggested fix:
The following shows a similar database that acts correctly.

mysql> select user(), now(), version(), database();
+----------------+---------------------+-------------+------------+
| user()         | now()               | version()   | database() |
+----------------+---------------------+-------------+------------+
| root@localhost | 2008-04-01 09:58:19 | 5.1.11-beta | mysql      | 
+----------------+---------------------+-------------+------------+
1 row in set (0.02 sec)

mysql> show create table sancp;
| sancp | CREATE TABLE `sancp` (
  `sid` int(10) unsigned NOT NULL,
  `sancpid` bigint(20) unsigned NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `duration` int(10) unsigned NOT NULL,
  `ip_proto` tinyint(3) unsigned NOT NULL,
  `src_ip` int(10) unsigned DEFAULT NULL,
  `src_port` smallint(5) unsigned DEFAULT NULL,
  `dst_ip` int(10) unsigned DEFAULT NULL,
  `dst_port` smallint(5) unsigned DEFAULT NULL,
  `src_pkts` int(10) unsigned NOT NULL,
  `src_bytes` int(10) unsigned NOT NULL,
  `dst_pkts` int(10) unsigned NOT NULL,
  `dst_bytes` int(10) unsigned NOT NULL,
  `src_flags` tinyint(3) unsigned NOT NULL,
  `dst_flags` tinyint(3) unsigned NOT NULL,
  KEY `p_key` (`sid`,`sancpid`),
  KEY `src_ip` (`src_ip`),
  KEY `dst_ip` (`dst_ip`),
  KEY `dst_port` (`dst_port`),
  KEY `src_port` (`src_port`),
  KEY `start_time` (`start_time`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`sancp_hacom_20060822`,`sancp_hacom_20060823`, ...edited... `sancp_hacom_20080401`)

mysql> explain select count(*) from sancp;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away | 
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.01 sec)

Running the query 'select count(*) from sancp;' returns almost instantly.

I downgraded the database shown in the "Description" section to 5.0.51a and got results similar to those in this "Suggested fix" section.  I apologize for not having those results available.

Thank you for your attention and help!

I ran my tests with 5.1.22, 5.1.23, and 5.0.51a on one box to show the problem.

Attachment: example_on_one_box.txt (text/plain), 18.41 KiB.

On 5.1.25-BK:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.25-rc |
+-----------+
1 row in set (0.01 sec)

mysql> create table t_1(c1 int, c2 int, key c1(c1), key c2(c2)) engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t_2(c1 int, c2 int, key c1(c1), key c2(c2)) engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t_3(c1 int, c2 int, key c1(c1), key c2(c2)) engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t_merge(c1 int, c2 int, key c1(c1), key c2(c2)) engine=MRG_MyISAM UNION=(t_1, t_2, t_3);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_1 values (1,1), (1,2),(1,3), (1,4);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t_2 values (2,1), (2,2),(2,3), (2,4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t_3 values (3,1), (3,2),(3,3), (3,4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> explain select count(*) from t_1;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.01 sec)

mysql> explain select count(*) from t_merge;
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_merge | index | NULL          | c1   | 5       | NULL |   12 | Using index |
+----+-------------+---------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

On 5.0.60-BK:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.60 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t_1(c1 int, c2 int, key c1(c1), key c2(c2)) engine=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> create table t_2(c1 int, c2 int, key c1(c1), key c2(c2)) engine=MyISAM;  Query OK, 0 rows affected (0.01 sec)

mysql> create table t_3(c1 int, c2 int, key c1(c1), key c2(c2)) engine=MyISAM;  Query OK, 0 rows affected (0.01 sec)

mysql> create table t_merge(c1 int, c2 int, key c1(c1), key c2(c2)) engine=MRG_MyISAM UNION=(t_1, t_2, t_3);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_1 values (1,1), (1,2),(1,3), (1,4);                        Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t_2 values (2,1), (2,2),(2,3), (2,4);                        Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t_3 values (3,1), (3,2),(3,3), (3,4);                        Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> explain select count(*) from t_merge;                                    +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

So, this is a regression bug in optimizer of recent 5.1.x.

Test case:

create table t_1(c1 int, c2 int, key c1(c1), key c2(c2)) engine=MyISAM;
create table t_2(c1 int, c2 int, key c1(c1), key c2(c2)) engine=MyISAM;
create table t_3(c1 int, c2 int, key c1(c1), key c2(c2)) engine=MyISAM;
create table t_merge(c1 int, c2 int, key c1(c1), key c2(c2)) engine=MRG_MyISAM UNION=(t_1, t_2, t_3);
insert into t_1 values (1,1), (1,2),(1,3), (1,4);
insert into t_2 values (2,1), (2,2),(2,3), (2,4);
insert into t_3 values (3,1), (3,2),(3,3), (3,4);
explain select count(*) from t_1;
explain select count(*) from t_merge;

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/46053

ChangeSet@1.2565, 2008-04-26 02:45:58+05:00, gshchepa@host.loc +8 -0
  Fixed bug#36006: Optimizer does table scan for SELECT COUNT(*)
                   for ENGINE=MRG_MYISAM (should be optimized out).
  
  Before WL#3281 MERGE engine had the HA_NOT_EXACT_COUNT flag
  unset, and it worked with COUNT optimization as desired.
  After the removal of the HA_NOT_EXACT_COUNT flag neither
  HA_STATS_RECORDS_IS_EXACT (opposite to former HA_NOT_EXACT_COUNT
  flag) nor modern HA_HAS_RECORDS flag were not added to MERGE
  table flag mask.
  
  1. The HA_HAS_RECORDS table flag has been set.
  2. The ha_myisammrg::records method has been overridden to
     calculate total number of records in underlying tables.

Pushed into 5.1.25-rc

Pushed into 6.0.6-alpha

Noted in 5.1.25, 6.0.6 changelogs.

The MERGE storage engine did a table scan for SELECT COUNT(*)
statements when it could calculate the number of records from the
underlying tables.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/55830

2860 Gleb Shchepa	2008-10-09
      Cleanup for closed bug #36006: Optimizer does table scan for
      select count(*)
      
      Unnecessary overloading of ha_myisammrg::records() and
      related stuff have been removed.