|
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (id INT NOT NULL, a DATE, KEY(id,a)) ENGINE=MyISAM;
|
INSERT INTO t1 VALUES (1,'2001-01-01');
|
INSERT INTO t1 VALUES (1,'2001-01-02');
|
INSERT INTO t1 VALUES (1,'2001-01-03');
|
INSERT INTO t1 VALUES (1,'2001-01-04');
|
INSERT INTO t1 VALUES (2,'2001-01-01');
|
INSERT INTO t1 VALUES (2,'2001-01-02');
|
INSERT INTO t1 VALUES (2,'2001-01-03');
|
INSERT INTO t1 VALUES (2,'2001-01-04');
|
INSERT INTO t1 VALUES (3,'2001-01-01');
|
INSERT INTO t1 VALUES (3,'2001-01-02');
|
INSERT INTO t1 VALUES (3,'2001-01-03');
|
INSERT INTO t1 VALUES (3,'2001-01-04');
|
INSERT INTO t1 VALUES (4,'2001-01-01');
|
INSERT INTO t1 VALUES (4,'2001-01-02');
|
INSERT INTO t1 VALUES (4,'2001-01-03');
|
INSERT INTO t1 VALUES (4,'2001-01-04');
|
EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>='2001-01-04' GROUP BY id;
|
returns:
+------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
|
| 1 | SIMPLE | t1 | range | NULL | id | 8 | NULL | 9 | Using where; Using index for group-by |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+
|
It's going to scan 9 out of 16 rows, which means group_min_max optimization is enabled.
Now if I change the constant from '2001-01-05' to 20010104 and rerun EXPLAIN:
EXPLAIN SELECT id,MIN(a),MAX(a) FROM t1 WHERE a>=20010104 GROUP BY id;
|
it returns:
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
| 1 | SIMPLE | t1 | index | NULL | id | 8 | NULL | 16 | Using where; Using index |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|
It will scan all 16 rows, that means group_min_max optimization is not applied.
It safely could. Comparison in both cases is done by DATE anyway.
The same is true for DECIMAL and DOUBLE literals.
|