Hello Sergey, I'm working on pluggable data types and moving some pieces of the code into new classes for data type handlers. I noticed that GROUP_MIN_MAX optimization works fine with a TIME column with temporal and string literals but does not work for an integer literal: # This script creates demo data: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (id INT, b TIME, KEY(id,b)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,10),(1,11),(1,12),(1,13),(1,14),(1,15),(1,16),(2,10),(2,11),(2,12),(2,13),(2,14),(2,15),(2,16); SELECT * FROM t1; # Now three EXPLAIN queries: explain SELECT id, MIN(b),MAX(b) FROM t1 WHERE b<TIME'10:10:10' GROUP BY id; explain SELECT id, MIN(b),MAX(b) FROM t1 WHERE b<'10:10:10' GROUP BY id; explain SELECT id, MIN(b),MAX(b) FROM t1 WHERE b<10 GROUP BY id; The first and the second EXPLAIN queries tell that 8 rows are to be examined; tracing the code in debugger shows that group_min_max optimization is active:
mysql> explain SELECT id, MIN(b),MAX(b) FROM t1 WHERE b<TIME'10:10:10' GROUP BY id; +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ | 1 | SIMPLE | t1 | range | NULL | id | 9 | NULL | 8 | Using where; Using index for group-by | +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ 1 row in set (0.00 sec)
mysql> explain SELECT id, MIN(b),MAX(b) FROM t1 WHERE b<'10:10:10' GROUP BY id; +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ | 1 | SIMPLE | t1 | range | NULL | id | 9 | NULL | 8 | Using where; Using index for group-by | +------+-------------+-------+-------+---------------+------+---------+------+------+---------------------------------------+ 1 row in set (0.00 sec)
The third EXPLAIN query tells that all 14 rows is to be examined, and tracing in debugging shows that group_min_max is not enabled:
mysql> explain SELECT id, MIN(b),MAX(b) FROM t1 WHERE b<10 GROUP BY id; +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | t1 | index | NULL | id | 9 | NULL | 14 | Using where; Using index | +------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.01 sec)
Any reasons not to use group_min_max for TIME column in a combination with an integer literal? I have a feeling that it should work. What about decimal and real literals? Thanks.