maria-developers team mailing list archive

[Alexander Barkov <bar@xxxxxxxxxxx>]

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.