Re: Projection of a WHERE attribute

[brice chardin]

With additional tests, it looks like these are two different problems.

I created the same table using MyISAM, populated with the same data :

CREATE TABLE `Values` (
  `Id` int(11) NOT NULL,
  `Date` bigint(20) unsigned NOT NULL,
  `Value` tinyint(1) DEFAULT NULL,
  `Quality` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`Id`,`Date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

show index (MyISAM) :
mysql> show index from Values;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Values |          0 | PRIMARY  |            1 | Id          | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| Values |          0 | PRIMARY  |            2 | Date        | A         |   500000000 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

CREATE TABLE `Values` (
  `Id` int(11) NOT NULL,
  `Date` bigint(20) unsigned NOT NULL
  `Value` tinyint(1) DEFAULT NULL,
  `Quality` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`Id`,`Date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

show index (InnoDB) :
mysql> SHOW INDEX FROM Values;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Values |          0 | PRIMARY  |            1 | Id          | A         |   499971579 |     NULL | NULL   |      | BTREE      |         |
| Values |          0 | PRIMARY  |            2 | Date        | A         |   499971579 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

The first problem was that the projection of the attribute `Id` increased the query execution time.

for InnoDB :
mysql> SELECT max(Date) FROM Values WHERE Id = 1;
+---------------+
| max(Date)     |
+---------------+
| 1302238691129 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT Id, max(Date) FROM Values WHERE Id = 1;
+----+---------------+
| Id | max(Date)     |
+----+---------------+
|  1 | 1302238691129 |
+----+---------------+
1 row in set (8.94 sec)

for MyISAM :
mysql> SELECT max(Date) FROM Values WHERE Id = 1;
+---------------+
| max(Date)     |
+---------------+
| 1302238691129 |
+---------------+
1 row in set (0.00 sec) (first exec.)
1 row in set (0.00 sec) (second exec.)

mysql> SELECT Id, max(Date) FROM Values WHERE Id = 1;
+----+---------------+
| Id | max(Date)     |
+----+---------------+
|  1 | 1302238691129 |
+----+---------------+
1 row in set (8 min 55.98 sec) (first exec.)
1 row in set (9.33 sec) (second exec.)

EXPLAINs showed that including `Id` in the projected attributes changed "Select tables optimized away" by "Using index".
It should be noted that the attribute Id is quite selective (average 1/30) and the table has 500,000,000 entries.

mysql> EXPLAIN SELECT Id, max(Date) FROM Values WHERE Id = 1; (InnoDB)
+----+-------------+--------+------+---------------+---------+---------+-------+----------+-------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows     | Extra       |
+----+-------------+--------+------+---------------+---------+---------+-------+----------+-------------+
|  1 | SIMPLE      | Values | ref  | PRIMARY       | PRIMARY | 4       | const | 11801910 | Using index |
+----+-------------+--------+------+---------------+---------+---------+-------+----------+-------------+
mysql> EXPLAIN SELECT Id, max(Date) FROM Values WHERE Id = 1; (MyISAM)
+----+-------------+--------+------+---------------+---------+---------+-------+----------+-------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows     | Extra       |
+----+-------------+--------+------+---------------+---------+---------+-------+----------+-------------+
|  1 | SIMPLE      | Values | ref  | PRIMARY       | PRIMARY | 4       | const | 19601433 | Using index |
+----+-------------+--------+------+---------------+---------+---------+-------+----------+-------------+

mysql> EXPLAIN SELECT max(Date) FROM Values WHERE Id = 1; (InnoDB)
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 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 |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
mysql> EXPLAIN SELECT max(Date) FROM Values WHERE Id = 1; (MyISAM)
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 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 |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+

I still don't understand where this is coming from and I'll probably, as you suggested, report this as a bug.


The other problem is specific to InnoDB, as using MyISAM shows the same EXPLAIN (and good performances) in both cases :
(As a reminder, MySQL with InnoDB was doing a full index scan instead of a range scan when FORCE INDEX (PRIMARY) was not added. For more details, see the first reply in this topic).

mysql> SELECT Id, max(Date) FROM Values FORCE INDEX (PRIMARY) GROUP BY Id;
+----+---------------+
| Id | max(Date)     |
+----+---------------+
|  1 | 1302238691129 |
|  2 | 1302238690713 |
|        ...         |
| 29 | 1302238692106 |
| 30 | 1302238683422 |
+----+---------------+
30 rows in set (0.70 sec) (first exec.)
30 rows in set (0.00 sec) (second exec.)

mysql> SELECT Id, max(Date) FROM Values GROUP BY Id;
+----+---------------+
| Id | max(Date)     |
+----+---------------+
|  1 | 1302238691129 |
|  2 | 1302238690713 |
|        ...         |
| 29 | 1302238692106 |
| 30 | 1302238683422 |
+----+---------------+
30 rows in set (0.00 sec) (first exec., suspecting cache hit)
30 rows in set (0.00 sec) (second exec.)

mysql> EXPLAIN SELECT Id, max(Date) FROM Values GROUP BY Id;
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | Values | range | NULL          | PRIMARY | 4       | NULL |   10 | Using index for group-by |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+

mysql> EXPLAIN SELECT Id, max(Date) FROM Values FORCE INDEX (PRIMARY) GROUP BY Id;
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | Values | range | NULL          | PRIMARY | 4       | NULL |   10 | Using index for group-by |
+----+-------------+--------+-------+---------------+---------+---------+------+------+--------------------------+

Edited 1 time(s). Last edit at 03/19/2010 10:33AM by brice chardin.