MySQL Forums
Forum List  »  Optimizer & Parser

Re: Projection of a WHERE attribute
Posted by: brice chardin
Date: March 19, 2010 10:06AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
7076
March 16, 2010 12:03PM
1787
March 19, 2010 07:37AM
Re: Projection of a WHERE attribute
2064
March 19, 2010 10:06AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.