CREATE TABLE t (a INT, b INT, c INT, d INT, KEY k1(a, b, c, d)) ENGINE=innodb; ANALYZE TABLE t; Table Op Msg_type Msg_text test.t analyze status OK EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 78 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15) or (`test`.`t`.`b` = 3)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 20 -10 12 -15 -23 2 10 1 3 10 1 4 10 1 5 10 1 6 10 1 7 10 1 8 10 1 9 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 113 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 -10 -10 12 -15 -23 2 10 1 3 10 1 4 10 1 5 10 1 6 10 1 7 10 1 8 10 1 9 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 78 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c IS NULL) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` is null))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c IS NULL) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 -10 -20 12 -15 -15 2 10 1 3 10 1 4 10 1 5 10 1 6 10 1 7 10 1 8 10 1 9 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 77 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b IS NULL OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` is null) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b IS NULL OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 11 30 30 12 -15 -20 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 65 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b IS NULL OR b = 2) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` is null) or (`test`.`t`.`b` = 2)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b IS NULL OR b = 2) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 30 -10 12 -23 -23 2 10 1 3 10 1 4 10 1 5 10 1 6 10 1 7 10 1 8 10 1 9 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 77 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 78 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15) or (`test`.`t`.`b` = 3)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a; a 1 10 11 12 2 3 4 5 6 7 8 9 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 26 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 114 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 2 10 1 10 2 10 1 11 2 -10 -10 12 15 -15 -20 12 2 -23 -23 2 2 10 1 3 2 10 1 4 2 10 1 5 2 10 1 6 2 10 1 7 2 10 1 8 2 10 1 9 2 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 228 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 114 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15) or (`test`.`t`.`b` = 3)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 2 10 1 1 3 10 1 10 2 10 1 10 3 10 1 11 2 -10 -10 11 3 20 20 12 15 -15 -20 12 2 -23 -23 2 2 10 1 2 3 10 1 3 2 10 1 3 3 10 1 4 2 10 1 4 3 10 1 5 2 10 1 5 3 10 1 6 2 10 1 6 3 10 1 7 2 10 1 7 3 10 1 8 2 10 1 8 3 10 1 9 2 10 1 9 3 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 228 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT DISTINCT a FROM t WHERE a = 1 AND (b = 2 OR b = 15); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 10 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select distinct `test`.`t`.`a` AS `a` from `test`.`t` where ((`test`.`t`.`a` = 1) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15))) FLUSH STATUS; SELECT DISTINCT a FROM t WHERE a = 1 AND (b = 2 OR b = 15); a 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 4 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE a = 1 AND (c = 3 OR c = 40) AND (d = 1 OR d = 9) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` = 1) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` = 1) or (`test`.`t`.`d` = 9))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE a = 1 AND (c = 3 OR c = 40) AND (d = 1 OR d = 9) GROUP BY a, b; a MAX(d) MIN(d) 1 9 1 1 9 1 1 9 1 1 9 1 1 9 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 27 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 2 10 1 12 15 -15 -20 12 2 -23 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 20 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a < 2 OR a > 11) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`a` < 2) or (`test`.`t`.`a` > 11)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a < 2 OR a > 11) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 2 10 1 12 15 -15 -20 12 2 -23 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 34 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT DISTINCT a, b FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select distinct `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) FLUSH STATUS; SELECT DISTINCT a, b FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40); a b 1 2 12 15 12 2 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 11 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT DISTINCT a FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 10 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select distinct `test`.`t`.`a` AS `a` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15))) FLUSH STATUS; SELECT DISTINCT a FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15); a 1 12 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 7 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a = 12) AND (b < 4 OR b > 10) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 8 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` < 4) or (`test`.`t`.`b` > 10)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a = 12) AND (b < 4 OR b > 10) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 1 10 1 1 2 10 1 1 3 10 1 12 15 -15 -20 12 2 -23 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 28 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a > 9) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 4 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` > 9)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a > 9) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 -10 -10 12 -15 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 31 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT COUNT(DISTINCT a, b) FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select count(distinct `test`.`t`.`a`,`test`.`t`.`b`) AS `COUNT(DISTINCT a, b)` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) FLUSH STATUS; SELECT COUNT(DISTINCT a, b) FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40); COUNT(DISTINCT a, b) 3 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 11 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT SUM(DISTINCT a), AVG(DISTINCT a) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select sum(distinct `test`.`t`.`a`) AS `SUM(DISTINCT a)`,avg(distinct `test`.`t`.`a`) AS `AVG(DISTINCT a)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) FLUSH STATUS; SELECT SUM(DISTINCT a), AVG(DISTINCT a) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40); SUM(DISTINCT a) AVG(DISTINCT a) 78 6.5000 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 26 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT SUM(DISTINCT a), AVG(DISTINCT a), COUNT(DISTINCT a) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select sum(distinct `test`.`t`.`a`) AS `SUM(DISTINCT a)`,avg(distinct `test`.`t`.`a`) AS `AVG(DISTINCT a)`,count(distinct `test`.`t`.`a`) AS `COUNT(DISTINCT a)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) FLUSH STATUS; SELECT SUM(DISTINCT a), AVG(DISTINCT a), COUNT(DISTINCT a) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40); SUM(DISTINCT a) AVG(DISTINCT a) COUNT(DISTINCT a) 78 6.5000 12 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 26 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (a > 11) AND (b = 2 OR b = 15) AND (c = 3 OR c = 1 OR c = 2 OR c = 4) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 5 NULL 4 6.53 Using where; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 11) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 1) or (`test`.`t`.`c` = 2) or (`test`.`t`.`c` = 4))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (a > 11) AND (b = 2 OR b = 15) AND (c = 3 OR c = 1 OR c = 2 OR c = 4) GROUP BY a; a MAX(d) MIN(d) 12 -15 -15 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (c = 3 OR c = 40) AND (d > 7 OR d =2) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 114 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` > 7) or (`test`.`t`.`d` = 2))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (c = 3 OR c = 40) AND (d > 7 OR d =2) GROUP BY a, b; a b MAX(d) MIN(d) 1 1 10 2 1 2 10 2 1 3 10 2 1 4 10 2 1 5 10 2 10 1 10 2 10 2 10 2 10 3 10 2 10 4 10 2 10 5 10 2 11 3 20 20 11 NULL 30 30 2 1 10 2 2 2 10 2 2 3 10 2 2 4 10 2 2 5 10 2 3 1 10 2 3 2 10 2 3 3 10 2 3 4 10 2 3 5 10 2 4 1 10 2 4 2 10 2 4 3 10 2 4 4 10 2 4 5 10 2 5 1 10 2 5 2 10 2 5 3 10 2 5 4 10 2 5 5 10 2 6 1 10 2 6 2 10 2 6 3 10 2 6 4 10 2 6 5 10 2 7 1 10 2 7 2 10 2 7 3 10 2 7 4 10 2 7 5 10 2 8 1 10 2 8 2 10 2 8 3 10 2 8 4 10 2 8 5 10 2 9 1 10 2 9 2 10 2 9 3 10 2 9 4 10 2 9 5 10 2 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 283 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (c = 3 OR c = 40) AND (d > 7 OR d =2 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 114 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` > 7) or (`test`.`t`.`d` = 2) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (c = 3 OR c = 40) AND (d > 7 OR d =2 OR d IS NULL) GROUP BY a, b; a b MAX(d) MIN(d) 1 1 10 2 1 2 10 2 1 3 10 2 1 4 10 2 1 5 10 2 10 1 10 2 10 2 10 2 10 3 10 2 10 4 10 2 10 5 10 2 11 3 20 20 11 NULL 30 30 12 2 NULL NULL 2 1 10 2 2 2 10 2 2 3 10 2 2 4 10 2 2 5 10 2 3 1 10 2 3 2 10 2 3 3 10 2 3 4 10 2 3 5 10 2 4 1 10 2 4 2 10 2 4 3 10 2 4 4 10 2 4 5 10 2 5 1 10 2 5 2 10 2 5 3 10 2 5 4 10 2 5 5 10 2 6 1 10 2 6 2 10 2 6 3 10 2 6 4 10 2 6 5 10 2 7 1 10 2 7 2 10 2 7 3 10 2 7 4 10 2 7 5 10 2 8 1 10 2 8 2 10 2 8 3 10 2 8 4 10 2 8 5 10 2 9 1 10 2 9 2 10 2 9 3 10 2 9 4 10 2 9 5 10 2 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 398 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MIN(d) FROM t WHERE (a > 5) AND (c = 3 OR c = 40) AND (d < -24 OR d = 3 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 58 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 5) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` < (-(24))) or (`test`.`t`.`d` = 3) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MIN(d) FROM t WHERE (a > 5) AND (c = 3 OR c = 40) AND (d < -24 OR d = 3 OR d IS NULL) GROUP BY a, b; a b MIN(d) 10 1 3 10 2 3 10 3 3 10 4 3 10 5 3 12 2 NULL 6 1 3 6 2 3 6 3 3 6 4 3 6 5 3 7 1 3 7 2 3 7 3 3 7 4 3 7 5 3 8 1 3 8 2 3 8 3 3 8 4 3 8 5 3 9 1 3 9 2 3 9 3 3 9 4 3 9 5 3 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 155 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a > 5) AND (c = 3 OR c = 40) AND (d < 1 OR d = 9) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 58 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 5) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` < 1) or (`test`.`t`.`d` = 9))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a > 5) AND (c = 3 OR c = 40) AND (d < 1 OR d = 9) GROUP BY a, b; a b MAX(d) MIN(d) 10 1 9 9 10 2 9 9 10 3 9 9 10 4 9 9 10 5 9 9 11 2 -10 -10 12 15 -15 -20 12 2 -23 -23 6 1 9 9 6 2 9 9 6 3 9 9 6 4 9 9 6 5 9 9 7 1 9 9 7 2 9 9 7 3 9 9 7 4 9 9 7 5 9 9 8 1 9 9 8 2 9 9 8 3 9 9 8 4 9 9 8 5 9 9 9 1 9 9 9 2 9 9 9 3 9 9 9 4 9 9 9 5 9 9 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 186 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d = 1 OR d = 9 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 9) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` = 1) or (`test`.`t`.`d` = 9) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d = 1 OR d = 9 OR d IS NULL) GROUP BY a, b; a b MAX(d) MIN(d) 10 1 9 1 10 2 9 1 10 3 9 1 10 4 9 1 10 5 9 1 12 2 NULL NULL SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 81 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d = 0 OR d = 9 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `MIN(d)`,max(`test`.`t`.`d`) AS `MAX(d)` from `test`.`t` where ((`test`.`t`.`a` > 9) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` = 0) or (`test`.`t`.`d` = 9) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d = 0 OR d = 9 OR d IS NULL) GROUP BY a, b; a b MIN(d) MAX(d) 10 1 9 9 10 2 9 9 10 3 9 9 10 4 9 9 10 5 9 9 12 2 NULL NULL SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 86 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d > 0 AND d < 2 OR d > 3 AND d < 5 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `MIN(d)`,max(`test`.`t`.`d`) AS `MAX(d)` from `test`.`t` where ((`test`.`t`.`a` > 9) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and (((`test`.`t`.`d` > 0) and (`test`.`t`.`d` < 2)) or ((`test`.`t`.`d` > 3) and (`test`.`t`.`d` < 5)) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d > 0 AND d < 2 OR d > 3 AND d < 5 OR d IS NULL) GROUP BY a, b; a b MIN(d) MAX(d) 10 1 1 4 10 2 1 4 10 3 1 4 10 4 1 4 10 5 1 4 12 2 NULL NULL SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 80 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MIN(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d < 2 OR d > 5 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 9) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` < 2) or (`test`.`t`.`d` > 5) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MIN(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d < 2 OR d > 5 OR d IS NULL) GROUP BY a, b; a b MIN(d) 10 1 1 10 2 1 10 3 1 10 4 1 10 5 1 11 2 -10 11 3 20 11 NULL 30 12 15 -20 12 2 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 47 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b , max(d) FROM t WHERE (a >= 10) AND (c = 3 or c=40) AND (d is NULL or d = -10 or d = -23) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `max(d)` from `test`.`t` where ((`test`.`t`.`a` >= 10) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` is null) or (`test`.`t`.`d` = (-(10))) or (`test`.`t`.`d` = (-(23))))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b , max(d) FROM t WHERE (a >= 10) AND (c = 3 or c=40) AND (d is NULL or d = -10 or d = -23) GROUP BY a, b; a b max(d) 11 2 -10 12 2 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 75 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b , min(d) FROM t WHERE (a >= 10) AND (c = 3 or c=40) AND (d is NULL or d = -10 or d = -23) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `min(d)` from `test`.`t` where ((`test`.`t`.`a` >= 10) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` is null) or (`test`.`t`.`d` = (-(10))) or (`test`.`t`.`d` = (-(23))))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b , min(d) FROM t WHERE (a >= 10) AND (c = 3 or c=40) AND (d is NULL or d = -10 or d = -23) GROUP BY a, b; a b min(d) 11 2 -10 12 2 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 77 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MIN(b), MAX(b) FROM t WHERE a > 9 GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 10 NULL 1 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,min(`test`.`t`.`b`) AS `MIN(b)`,max(`test`.`t`.`b`) AS `MAX(b)` from `test`.`t` where (`test`.`t`.`a` > 9) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MIN(b), MAX(b) FROM t WHERE a > 9 GROUP BY a; a MIN(b) MAX(b) 10 1 5 11 2 15 12 2 15 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 9 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; ALTER TABLE t ADD KEY k2(a DESC, b, c DESC, d); ANALYZE TABLE t; Table Op Msg_type Msg_text test.t analyze status OK SELECT a, b, MIN(d) FROM t WHERE (a > 4) AND (c = 3 or c = 40) AND (d > 7) GROUP BY a, b; a b MIN(d) 10 1 8 10 2 8 10 3 8 10 4 8 10 5 8 11 3 20 11 NULL 30 5 1 8 5 2 8 5 3 8 5 4 8 5 5 8 6 1 8 6 2 8 6 3 8 6 4 8 6 5 8 7 1 8 7 2 8 7 3 8 7 4 8 7 5 8 8 1 8 8 2 8 8 3 8 8 4 8 8 5 8 9 1 8 9 2 8 9 3 8 9 4 8 9 5 8 SELECT a, b, MIN(d) FROM t WHERE (a > 4) AND (c = 3 or c = 40) AND (d > 7) GROUP BY a, b; a b MIN(d) 10 1 8 10 2 8 10 3 8 10 4 8 10 5 8 11 3 20 11 NULL 30 5 1 8 5 2 8 5 3 8 5 4 8 5 5 8 6 1 8 6 2 8 6 3 8 6 4 8 6 5 8 7 1 8 7 2 8 7 3 8 7 4 8 7 5 8 8 1 8 8 2 8 8 3 8 8 4 8 8 5 8 9 1 8 9 2 8 9 3 8 9 4 8 9 5 8 ALTER TABLE t DROP KEY k2; EXPLAIN SELECT a, b FROM t WHERE (a > 4) AND (c = 3 OR c > 6) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 5 NULL 1508 40.00 Using where; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` where ((`test`.`t`.`a` > 4) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` > 6))) group by `test`.`t`.`a`,`test`.`t`.`b` EXPLAIN SELECT a, b FROM t WHERE (a > 4) AND (c = 3 OR c = 40) AND (d = -1 OR d = -2 OR d > 7) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 5 NULL 1508 8.74 Using where; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` where ((`test`.`t`.`a` > 4) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` = (-(1))) or (`test`.`t`.`d` = (-(2))) or (`test`.`t`.`d` > 7))) group by `test`.`t`.`a`,`test`.`t`.`b` ALTER TABLE t DROP KEY k1; ALTER TABLE t ADD KEY k1(a, b, c, d DESC); ANALYZE TABLE t; Table Op Msg_type Msg_text test.t analyze status OK EXPLAIN SELECT a, b, MIN(d) FROM t WHERE (a > 9) AND (c = 3) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 6 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`c` = 3) and (`test`.`t`.`a` > 9)) group by `test`.`t`.`a`,`test`.`t`.`b` SELECT a, b, MIN(d) FROM t WHERE (a > 9) AND (c = 3) GROUP BY a, b; a b MIN(d) 10 1 1 10 2 1 10 3 1 10 4 1 10 5 1 11 2 -10 11 3 20 12 2 NULL 12 15 -15 EXPLAIN SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 6 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `MIN(d)`,max(`test`.`t`.`d`) AS `MAX(d)` from `test`.`t` where ((`test`.`t`.`c` = 3) and (`test`.`t`.`a` > 9)) group by `test`.`t`.`a`,`test`.`t`.`b` SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3) GROUP BY a, b; a b MIN(d) MAX(d) 10 1 1 10 10 2 1 10 10 3 1 10 10 4 1 10 10 5 1 10 11 2 -10 -10 11 3 20 20 12 2 NULL NULL 12 15 -15 -15 ALTER TABLE t DROP KEY k1; ALTER TABLE t ADD KEY k1(a DESC, b, c DESC, d); ANALYZE TABLE t; Table Op Msg_type Msg_text test.t analyze status OK EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 78 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15) or (`test`.`t`.`b` = 3)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 20 -10 12 -15 -23 2 10 1 3 10 1 4 10 1 5 10 1 6 10 1 7 10 1 8 10 1 9 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 113 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 -10 -10 12 -15 -23 2 10 1 3 10 1 4 10 1 5 10 1 6 10 1 7 10 1 8 10 1 9 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 78 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c IS NULL) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` is null))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c IS NULL) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 -10 -20 12 -15 -15 2 10 1 3 10 1 4 10 1 5 10 1 6 10 1 7 10 1 8 10 1 9 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 77 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b IS NULL OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` is null) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b IS NULL OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 11 30 30 12 -15 -20 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 64 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b IS NULL OR b = 2) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` is null) or (`test`.`t`.`b` = 2)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b IS NULL OR b = 2) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 30 -10 12 -23 -23 2 10 1 3 10 1 4 10 1 5 10 1 6 10 1 7 10 1 8 10 1 9 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 77 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 78 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15) or (`test`.`t`.`b` = 3)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a; a 1 10 11 12 2 3 4 5 6 7 8 9 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 37 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 114 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 2 10 1 10 2 10 1 11 2 -10 -10 12 15 -15 -20 12 2 -23 -23 2 2 10 1 3 2 10 1 4 2 10 1 5 2 10 1 6 2 10 1 7 2 10 1 8 2 10 1 9 2 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 228 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 114 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15) or (`test`.`t`.`b` = 3)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 2 10 1 1 3 10 1 10 2 10 1 10 3 10 1 11 2 -10 -10 11 3 20 20 12 15 -15 -20 12 2 -23 -23 2 2 10 1 2 3 10 1 3 2 10 1 3 3 10 1 4 2 10 1 4 3 10 1 5 2 10 1 5 3 10 1 6 2 10 1 6 3 10 1 7 2 10 1 7 3 10 1 8 2 10 1 8 3 10 1 9 2 10 1 9 3 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 228 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT DISTINCT a FROM t WHERE a = 1 AND (b = 2 OR b = 15); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 10 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select distinct `test`.`t`.`a` AS `a` from `test`.`t` where ((`test`.`t`.`a` = 1) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15))) FLUSH STATUS; SELECT DISTINCT a FROM t WHERE a = 1 AND (b = 2 OR b = 15); a 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 4 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE a = 1 AND (c = 3 OR c = 40) AND (d = 1 OR d = 9) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` = 1) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` = 1) or (`test`.`t`.`d` = 9))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE a = 1 AND (c = 3 OR c = 40) AND (d = 1 OR d = 9) GROUP BY a, b; a MAX(d) MIN(d) 1 9 1 1 9 1 1 9 1 1 9 1 1 9 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 27 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 2 10 1 12 15 -15 -20 12 2 -23 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 20 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a < 2 OR a > 11) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`a` < 2) or (`test`.`t`.`a` > 11)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a < 2 OR a > 11) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 2 10 1 12 15 -15 -20 12 2 -23 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 34 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT DISTINCT a, b FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select distinct `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) FLUSH STATUS; SELECT DISTINCT a, b FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40); a b 1 2 12 15 12 2 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 12 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT DISTINCT a FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 10 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select distinct `test`.`t`.`a` AS `a` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15))) FLUSH STATUS; SELECT DISTINCT a FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15); a 1 12 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 7 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a = 12) AND (b < 4 OR b > 10) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 8 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` < 4) or (`test`.`t`.`b` > 10)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a = 12) AND (b < 4 OR b > 10) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 1 10 1 1 2 10 1 1 3 10 1 12 15 -15 -20 12 2 -23 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 28 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a > 9) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 4 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` > 9)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a > 9) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 -10 -10 12 -15 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 31 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT COUNT(DISTINCT a, b) FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select count(distinct `test`.`t`.`a`,`test`.`t`.`b`) AS `COUNT(DISTINCT a, b)` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) FLUSH STATUS; SELECT COUNT(DISTINCT a, b) FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40); COUNT(DISTINCT a, b) 3 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 12 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT SUM(DISTINCT a), AVG(DISTINCT a) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select sum(distinct `test`.`t`.`a`) AS `SUM(DISTINCT a)`,avg(distinct `test`.`t`.`a`) AS `AVG(DISTINCT a)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) FLUSH STATUS; SELECT SUM(DISTINCT a), AVG(DISTINCT a) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40); SUM(DISTINCT a) AVG(DISTINCT a) 78 6.5000 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 37 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT SUM(DISTINCT a), AVG(DISTINCT a), COUNT(DISTINCT a) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select sum(distinct `test`.`t`.`a`) AS `SUM(DISTINCT a)`,avg(distinct `test`.`t`.`a`) AS `AVG(DISTINCT a)`,count(distinct `test`.`t`.`a`) AS `COUNT(DISTINCT a)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) FLUSH STATUS; SELECT SUM(DISTINCT a), AVG(DISTINCT a), COUNT(DISTINCT a) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40); SUM(DISTINCT a) AVG(DISTINCT a) COUNT(DISTINCT a) 78 6.5000 12 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 37 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (a > 11) AND (b = 2 OR b = 15) AND (c = 3 OR c = 1 OR c = 2 OR c = 4) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 5 NULL 4 6.53 Using where; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 11) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 1) or (`test`.`t`.`c` = 2) or (`test`.`t`.`c` = 4))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (a > 11) AND (b = 2 OR b = 15) AND (c = 3 OR c = 1 OR c = 2 OR c = 4) GROUP BY a; a MAX(d) MIN(d) 12 -15 -15 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 1 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (c = 3 OR c = 40) AND (d > 7 OR d =2) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 114 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` > 7) or (`test`.`t`.`d` = 2))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (c = 3 OR c = 40) AND (d > 7 OR d =2) GROUP BY a, b; a b MAX(d) MIN(d) 1 1 10 2 1 2 10 2 1 3 10 2 1 4 10 2 1 5 10 2 10 1 10 2 10 2 10 2 10 3 10 2 10 4 10 2 10 5 10 2 11 3 20 20 11 NULL 30 30 2 1 10 2 2 2 10 2 2 3 10 2 2 4 10 2 2 5 10 2 3 1 10 2 3 2 10 2 3 3 10 2 3 4 10 2 3 5 10 2 4 1 10 2 4 2 10 2 4 3 10 2 4 4 10 2 4 5 10 2 5 1 10 2 5 2 10 2 5 3 10 2 5 4 10 2 5 5 10 2 6 1 10 2 6 2 10 2 6 3 10 2 6 4 10 2 6 5 10 2 7 1 10 2 7 2 10 2 7 3 10 2 7 4 10 2 7 5 10 2 8 1 10 2 8 2 10 2 8 3 10 2 8 4 10 2 8 5 10 2 9 1 10 2 9 2 10 2 9 3 10 2 9 4 10 2 9 5 10 2 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 284 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (c = 3 OR c = 40) AND (d > 7 OR d =2 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 114 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` > 7) or (`test`.`t`.`d` = 2) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (c = 3 OR c = 40) AND (d > 7 OR d =2 OR d IS NULL) GROUP BY a, b; a b MAX(d) MIN(d) 1 1 10 2 1 2 10 2 1 3 10 2 1 4 10 2 1 5 10 2 10 1 10 2 10 2 10 2 10 3 10 2 10 4 10 2 10 5 10 2 11 3 20 20 11 NULL 30 30 12 2 NULL NULL 2 1 10 2 2 2 10 2 2 3 10 2 2 4 10 2 2 5 10 2 3 1 10 2 3 2 10 2 3 3 10 2 3 4 10 2 3 5 10 2 4 1 10 2 4 2 10 2 4 3 10 2 4 4 10 2 4 5 10 2 5 1 10 2 5 2 10 2 5 3 10 2 5 4 10 2 5 5 10 2 6 1 10 2 6 2 10 2 6 3 10 2 6 4 10 2 6 5 10 2 7 1 10 2 7 2 10 2 7 3 10 2 7 4 10 2 7 5 10 2 8 1 10 2 8 2 10 2 8 3 10 2 8 4 10 2 8 5 10 2 9 1 10 2 9 2 10 2 9 3 10 2 9 4 10 2 9 5 10 2 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 399 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MIN(d) FROM t WHERE (a > 5) AND (c = 3 OR c = 40) AND (d < -24 OR d = 3 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 58 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 5) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` < (-(24))) or (`test`.`t`.`d` = 3) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MIN(d) FROM t WHERE (a > 5) AND (c = 3 OR c = 40) AND (d < -24 OR d = 3 OR d IS NULL) GROUP BY a, b; a b MIN(d) 10 1 3 10 2 3 10 3 3 10 4 3 10 5 3 12 2 NULL 6 1 3 6 2 3 6 3 3 6 4 3 6 5 3 7 1 3 7 2 3 7 3 3 7 4 3 7 5 3 8 1 3 8 2 3 8 3 3 8 4 3 8 5 3 9 1 3 9 2 3 9 3 3 9 4 3 9 5 3 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 156 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a > 5) AND (c = 3 OR c = 40) AND (d < 1 OR d = 9) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 58 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 5) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` < 1) or (`test`.`t`.`d` = 9))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a > 5) AND (c = 3 OR c = 40) AND (d < 1 OR d = 9) GROUP BY a, b; a b MAX(d) MIN(d) 10 1 9 9 10 2 9 9 10 3 9 9 10 4 9 9 10 5 9 9 11 2 -10 -10 12 15 -15 -20 12 2 -23 -23 6 1 9 9 6 2 9 9 6 3 9 9 6 4 9 9 6 5 9 9 7 1 9 9 7 2 9 9 7 3 9 9 7 4 9 9 7 5 9 9 8 1 9 9 8 2 9 9 8 3 9 9 8 4 9 9 8 5 9 9 9 1 9 9 9 2 9 9 9 3 9 9 9 4 9 9 9 5 9 9 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 186 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d = 1 OR d = 9 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 9) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` = 1) or (`test`.`t`.`d` = 9) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d = 1 OR d = 9 OR d IS NULL) GROUP BY a, b; a b MAX(d) MIN(d) 10 1 9 1 10 2 9 1 10 3 9 1 10 4 9 1 10 5 9 1 12 2 NULL NULL SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 82 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d = 0 OR d = 9 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `MIN(d)`,max(`test`.`t`.`d`) AS `MAX(d)` from `test`.`t` where ((`test`.`t`.`a` > 9) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` = 0) or (`test`.`t`.`d` = 9) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d = 0 OR d = 9 OR d IS NULL) GROUP BY a, b; a b MIN(d) MAX(d) 10 1 9 9 10 2 9 9 10 3 9 9 10 4 9 9 10 5 9 9 12 2 NULL NULL SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 87 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d > 0 AND d < 2 OR d > 3 AND d < 5 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `MIN(d)`,max(`test`.`t`.`d`) AS `MAX(d)` from `test`.`t` where ((`test`.`t`.`a` > 9) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and (((`test`.`t`.`d` > 0) and (`test`.`t`.`d` < 2)) or ((`test`.`t`.`d` > 3) and (`test`.`t`.`d` < 5)) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d > 0 AND d < 2 OR d > 3 AND d < 5 OR d IS NULL) GROUP BY a, b; a b MIN(d) MAX(d) 10 1 1 4 10 2 1 4 10 3 1 4 10 4 1 4 10 5 1 4 12 2 NULL NULL SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 82 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MIN(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d < 2 OR d > 5 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 9) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` < 2) or (`test`.`t`.`d` > 5) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MIN(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d < 2 OR d > 5 OR d IS NULL) GROUP BY a, b; a b MIN(d) 10 1 1 10 2 1 10 3 1 10 4 1 10 5 1 11 2 -10 11 3 20 11 NULL 30 12 15 -20 12 2 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 47 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b , max(d) FROM t WHERE (a >= 10) AND (c = 3 or c=40) AND (d is NULL or d = -10 or d = -23) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `max(d)` from `test`.`t` where ((`test`.`t`.`a` >= 10) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` is null) or (`test`.`t`.`d` = (-(10))) or (`test`.`t`.`d` = (-(23))))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b , max(d) FROM t WHERE (a >= 10) AND (c = 3 or c=40) AND (d is NULL or d = -10 or d = -23) GROUP BY a, b; a b max(d) 11 2 -10 12 2 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 76 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b , min(d) FROM t WHERE (a >= 10) AND (c = 3 or c=40) AND (d is NULL or d = -10 or d = -23) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `min(d)` from `test`.`t` where ((`test`.`t`.`a` >= 10) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` is null) or (`test`.`t`.`d` = (-(10))) or (`test`.`t`.`d` = (-(23))))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b , min(d) FROM t WHERE (a >= 10) AND (c = 3 or c=40) AND (d is NULL or d = -10 or d = -23) GROUP BY a, b; a b min(d) 11 2 -10 12 2 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 78 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MIN(b), MAX(b) FROM t WHERE a > 9 GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 10 NULL 1 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,min(`test`.`t`.`b`) AS `MIN(b)`,max(`test`.`t`.`b`) AS `MAX(b)` from `test`.`t` where (`test`.`t`.`a` > 9) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MIN(b), MAX(b) FROM t WHERE a > 9 GROUP BY a; a MIN(b) MAX(b) 10 1 5 11 2 15 12 2 15 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 9 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; ALTER TABLE t DROP KEY k1; ALTER TABLE t ADD KEY k1(a, b DESC, c, d DESC); ANALYZE TABLE t; Table Op Msg_type Msg_text test.t analyze status OK EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 78 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15) or (`test`.`t`.`b` = 3)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 20 -10 12 -15 -23 2 10 1 3 10 1 4 10 1 5 10 1 6 10 1 7 10 1 8 10 1 9 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 113 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 -10 -10 12 -15 -23 2 10 1 3 10 1 4 10 1 5 10 1 6 10 1 7 10 1 8 10 1 9 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 78 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c IS NULL) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` is null))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c IS NULL) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 -10 -20 12 -15 -15 2 10 1 3 10 1 4 10 1 5 10 1 6 10 1 7 10 1 8 10 1 9 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 77 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b IS NULL OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` is null) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b IS NULL OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 11 30 30 12 -15 -20 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 65 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b IS NULL OR b = 2) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` is null) or (`test`.`t`.`b` = 2)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b IS NULL OR b = 2) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 30 -10 12 -23 -23 2 10 1 3 10 1 4 10 1 5 10 1 6 10 1 7 10 1 8 10 1 9 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 77 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 78 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15) or (`test`.`t`.`b` = 3)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a; a 1 10 11 12 2 3 4 5 6 7 8 9 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 48 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 114 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 2 10 1 10 2 10 1 11 2 -10 -10 12 15 -15 -20 12 2 -23 -23 2 2 10 1 3 2 10 1 4 2 10 1 5 2 10 1 6 2 10 1 7 2 10 1 8 2 10 1 9 2 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 228 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 114 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15) or (`test`.`t`.`b` = 3)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 2 10 1 1 3 10 1 10 2 10 1 10 3 10 1 11 2 -10 -10 11 3 20 20 12 15 -15 -20 12 2 -23 -23 2 2 10 1 2 3 10 1 3 2 10 1 3 3 10 1 4 2 10 1 4 3 10 1 5 2 10 1 5 3 10 1 6 2 10 1 6 3 10 1 7 2 10 1 7 3 10 1 8 2 10 1 8 3 10 1 9 2 10 1 9 3 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 228 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT DISTINCT a FROM t WHERE a = 1 AND (b = 2 OR b = 15); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 10 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select distinct `test`.`t`.`a` AS `a` from `test`.`t` where ((`test`.`t`.`a` = 1) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15))) FLUSH STATUS; SELECT DISTINCT a FROM t WHERE a = 1 AND (b = 2 OR b = 15); a 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 5 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE a = 1 AND (c = 3 OR c = 40) AND (d = 1 OR d = 9) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` = 1) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` = 1) or (`test`.`t`.`d` = 9))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE a = 1 AND (c = 3 OR c = 40) AND (d = 1 OR d = 9) GROUP BY a, b; a MAX(d) MIN(d) 1 9 1 1 9 1 1 9 1 1 9 1 1 9 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 27 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 2 10 1 12 15 -15 -20 12 2 -23 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 20 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a < 2 OR a > 11) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`a` < 2) or (`test`.`t`.`a` > 11)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a < 2 OR a > 11) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 2 10 1 12 15 -15 -20 12 2 -23 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 34 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT DISTINCT a, b FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select distinct `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) FLUSH STATUS; SELECT DISTINCT a, b FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40); a b 1 2 12 15 12 2 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 11 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT DISTINCT a FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 10 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select distinct `test`.`t`.`a` AS `a` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15))) FLUSH STATUS; SELECT DISTINCT a FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15); a 1 12 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 8 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a = 12) AND (b < 4 OR b > 10) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 8 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` < 4) or (`test`.`t`.`b` > 10)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a = 12) AND (b < 4 OR b > 10) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 1 10 1 1 2 10 1 1 3 10 1 12 15 -15 -20 12 2 -23 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 28 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a > 9) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 4 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` > 9)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a > 9) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 -10 -10 12 -15 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 31 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT COUNT(DISTINCT a, b) FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select count(distinct `test`.`t`.`a`,`test`.`t`.`b`) AS `COUNT(DISTINCT a, b)` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) FLUSH STATUS; SELECT COUNT(DISTINCT a, b) FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40); COUNT(DISTINCT a, b) 3 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 11 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT SUM(DISTINCT a), AVG(DISTINCT a) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select sum(distinct `test`.`t`.`a`) AS `SUM(DISTINCT a)`,avg(distinct `test`.`t`.`a`) AS `AVG(DISTINCT a)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) FLUSH STATUS; SELECT SUM(DISTINCT a), AVG(DISTINCT a) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40); SUM(DISTINCT a) AVG(DISTINCT a) 78 6.5000 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 48 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT SUM(DISTINCT a), AVG(DISTINCT a), COUNT(DISTINCT a) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select sum(distinct `test`.`t`.`a`) AS `SUM(DISTINCT a)`,avg(distinct `test`.`t`.`a`) AS `AVG(DISTINCT a)`,count(distinct `test`.`t`.`a`) AS `COUNT(DISTINCT a)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) FLUSH STATUS; SELECT SUM(DISTINCT a), AVG(DISTINCT a), COUNT(DISTINCT a) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40); SUM(DISTINCT a) AVG(DISTINCT a) COUNT(DISTINCT a) 78 6.5000 12 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 48 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (a > 11) AND (b = 2 OR b = 15) AND (c = 3 OR c = 1 OR c = 2 OR c = 4) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 5 NULL 4 6.53 Using where; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 11) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 1) or (`test`.`t`.`c` = 2) or (`test`.`t`.`c` = 4))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (a > 11) AND (b = 2 OR b = 15) AND (c = 3 OR c = 1 OR c = 2 OR c = 4) GROUP BY a; a MAX(d) MIN(d) 12 -15 -15 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (c = 3 OR c = 40) AND (d > 7 OR d =2) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 114 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` > 7) or (`test`.`t`.`d` = 2))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (c = 3 OR c = 40) AND (d > 7 OR d =2) GROUP BY a, b; a b MAX(d) MIN(d) 1 1 10 2 1 2 10 2 1 3 10 2 1 4 10 2 1 5 10 2 10 1 10 2 10 2 10 2 10 3 10 2 10 4 10 2 10 5 10 2 11 3 20 20 11 NULL 30 30 2 1 10 2 2 2 10 2 2 3 10 2 2 4 10 2 2 5 10 2 3 1 10 2 3 2 10 2 3 3 10 2 3 4 10 2 3 5 10 2 4 1 10 2 4 2 10 2 4 3 10 2 4 4 10 2 4 5 10 2 5 1 10 2 5 2 10 2 5 3 10 2 5 4 10 2 5 5 10 2 6 1 10 2 6 2 10 2 6 3 10 2 6 4 10 2 6 5 10 2 7 1 10 2 7 2 10 2 7 3 10 2 7 4 10 2 7 5 10 2 8 1 10 2 8 2 10 2 8 3 10 2 8 4 10 2 8 5 10 2 9 1 10 2 9 2 10 2 9 3 10 2 9 4 10 2 9 5 10 2 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 228 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (c = 3 OR c = 40) AND (d > 7 OR d =2 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 114 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` > 7) or (`test`.`t`.`d` = 2) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (c = 3 OR c = 40) AND (d > 7 OR d =2 OR d IS NULL) GROUP BY a, b; a b MAX(d) MIN(d) 1 1 10 2 1 2 10 2 1 3 10 2 1 4 10 2 1 5 10 2 10 1 10 2 10 2 10 2 10 3 10 2 10 4 10 2 10 5 10 2 11 3 20 20 11 NULL 30 30 12 2 NULL NULL 2 1 10 2 2 2 10 2 2 3 10 2 2 4 10 2 2 5 10 2 3 1 10 2 3 2 10 2 3 3 10 2 3 4 10 2 3 5 10 2 4 1 10 2 4 2 10 2 4 3 10 2 4 4 10 2 4 5 10 2 5 1 10 2 5 2 10 2 5 3 10 2 5 4 10 2 5 5 10 2 6 1 10 2 6 2 10 2 6 3 10 2 6 4 10 2 6 5 10 2 7 1 10 2 7 2 10 2 7 3 10 2 7 4 10 2 7 5 10 2 8 1 10 2 8 2 10 2 8 3 10 2 8 4 10 2 8 5 10 2 9 1 10 2 9 2 10 2 9 3 10 2 9 4 10 2 9 5 10 2 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 287 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MIN(d) FROM t WHERE (a > 5) AND (c = 3 OR c = 40) AND (d < -24 OR d = 3 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 58 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 5) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` < (-(24))) or (`test`.`t`.`d` = 3) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MIN(d) FROM t WHERE (a > 5) AND (c = 3 OR c = 40) AND (d < -24 OR d = 3 OR d IS NULL) GROUP BY a, b; a b MIN(d) 10 1 3 10 2 3 10 3 3 10 4 3 10 5 3 12 2 NULL 6 1 3 6 2 3 6 3 3 6 4 3 6 5 3 7 1 3 7 2 3 7 3 3 7 4 3 7 5 3 8 1 3 8 2 3 8 3 3 8 4 3 8 5 3 9 1 3 9 2 3 9 3 3 9 4 3 9 5 3 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 156 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a > 5) AND (c = 3 OR c = 40) AND (d < 1 OR d = 9) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 58 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 5) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` < 1) or (`test`.`t`.`d` = 9))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a > 5) AND (c = 3 OR c = 40) AND (d < 1 OR d = 9) GROUP BY a, b; a b MAX(d) MIN(d) 10 1 9 9 10 2 9 9 10 3 9 9 10 4 9 9 10 5 9 9 11 2 -10 -10 12 15 -15 -20 12 2 -23 -23 6 1 9 9 6 2 9 9 6 3 9 9 6 4 9 9 6 5 9 9 7 1 9 9 7 2 9 9 7 3 9 9 7 4 9 9 7 5 9 9 8 1 9 9 8 2 9 9 8 3 9 9 8 4 9 9 8 5 9 9 9 1 9 9 9 2 9 9 9 3 9 9 9 4 9 9 9 5 9 9 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 186 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d = 1 OR d = 9 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 9) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` = 1) or (`test`.`t`.`d` = 9) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d = 1 OR d = 9 OR d IS NULL) GROUP BY a, b; a b MAX(d) MIN(d) 10 1 9 1 10 2 9 1 10 3 9 1 10 4 9 1 10 5 9 1 12 2 NULL NULL SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 80 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d = 0 OR d = 9 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `MIN(d)`,max(`test`.`t`.`d`) AS `MAX(d)` from `test`.`t` where ((`test`.`t`.`a` > 9) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` = 0) or (`test`.`t`.`d` = 9) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d = 0 OR d = 9 OR d IS NULL) GROUP BY a, b; a b MIN(d) MAX(d) 10 1 9 9 10 2 9 9 10 3 9 9 10 4 9 9 10 5 9 9 12 2 NULL NULL SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 85 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d > 0 AND d < 2 OR d > 3 AND d < 5 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `MIN(d)`,max(`test`.`t`.`d`) AS `MAX(d)` from `test`.`t` where ((`test`.`t`.`a` > 9) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and (((`test`.`t`.`d` > 0) and (`test`.`t`.`d` < 2)) or ((`test`.`t`.`d` > 3) and (`test`.`t`.`d` < 5)) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d > 0 AND d < 2 OR d > 3 AND d < 5 OR d IS NULL) GROUP BY a, b; a b MIN(d) MAX(d) 10 1 1 4 10 2 1 4 10 3 1 4 10 4 1 4 10 5 1 4 12 2 NULL NULL SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 80 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MIN(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d < 2 OR d > 5 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 9) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` < 2) or (`test`.`t`.`d` > 5) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MIN(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d < 2 OR d > 5 OR d IS NULL) GROUP BY a, b; a b MIN(d) 10 1 1 10 2 1 10 3 1 10 4 1 10 5 1 11 2 -10 11 3 20 11 NULL 30 12 15 -20 12 2 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 47 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b , max(d) FROM t WHERE (a >= 10) AND (c = 3 or c=40) AND (d is NULL or d = -10 or d = -23) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `max(d)` from `test`.`t` where ((`test`.`t`.`a` >= 10) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` is null) or (`test`.`t`.`d` = (-(10))) or (`test`.`t`.`d` = (-(23))))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b , max(d) FROM t WHERE (a >= 10) AND (c = 3 or c=40) AND (d is NULL or d = -10 or d = -23) GROUP BY a, b; a b max(d) 11 2 -10 12 2 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 76 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b , min(d) FROM t WHERE (a >= 10) AND (c = 3 or c=40) AND (d is NULL or d = -10 or d = -23) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `min(d)` from `test`.`t` where ((`test`.`t`.`a` >= 10) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` is null) or (`test`.`t`.`d` = (-(10))) or (`test`.`t`.`d` = (-(23))))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b , min(d) FROM t WHERE (a >= 10) AND (c = 3 or c=40) AND (d is NULL or d = -10 or d = -23) GROUP BY a, b; a b min(d) 11 2 -10 12 2 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 76 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MIN(b), MAX(b) FROM t WHERE a > 9 GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 10 NULL 1 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,min(`test`.`t`.`b`) AS `MIN(b)`,max(`test`.`t`.`b`) AS `MAX(b)` from `test`.`t` where (`test`.`t`.`a` > 9) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MIN(b), MAX(b) FROM t WHERE a > 9 GROUP BY a; a MIN(b) MAX(b) 10 1 5 11 2 15 12 2 15 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 9 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; DROP TABLE t; CREATE TABLE t (a INT, b INT, c INT, d INT, KEY k1(a, b, c, d)) ENGINE=myisam; ANALYZE TABLE t; Table Op Msg_type Msg_text test.t analyze status OK EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 78 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15) or (`test`.`t`.`b` = 3)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 20 -10 12 -15 -23 2 10 1 3 10 1 4 10 1 5 10 1 6 10 1 7 10 1 8 10 1 9 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 111 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 -10 -10 12 -15 -23 2 10 1 3 10 1 4 10 1 5 10 1 6 10 1 7 10 1 8 10 1 9 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 76 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c IS NULL) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` is null))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c IS NULL) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 -10 -20 12 -15 -15 2 10 1 3 10 1 4 10 1 5 10 1 6 10 1 7 10 1 8 10 1 9 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 75 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b IS NULL OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` is null) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b IS NULL OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 11 30 30 12 -15 -20 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 63 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (b IS NULL OR b = 2) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` is null) or (`test`.`t`.`b` = 2)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (b IS NULL OR b = 2) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 30 -10 12 -23 -23 2 10 1 3 10 1 4 10 1 5 10 1 6 10 1 7 10 1 8 10 1 9 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 75 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 78 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15) or (`test`.`t`.`b` = 3)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a; a 1 10 11 12 2 3 4 5 6 7 8 9 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 24 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 112 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 2 10 1 10 2 10 1 11 2 -10 -10 12 15 -15 -20 12 2 -23 -23 2 2 10 1 3 2 10 1 4 2 10 1 5 2 10 1 6 2 10 1 7 2 10 1 8 2 10 1 9 2 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 226 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 112 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15) or (`test`.`t`.`b` = 3)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (b = 2 OR b = 15 OR b = 3) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 2 10 1 1 3 10 1 10 2 10 1 10 3 10 1 11 2 -10 -10 11 3 20 20 12 15 -15 -20 12 2 -23 -23 2 2 10 1 2 3 10 1 3 2 10 1 3 3 10 1 4 2 10 1 4 3 10 1 5 2 10 1 5 3 10 1 6 2 10 1 6 3 10 1 7 2 10 1 7 3 10 1 8 2 10 1 8 3 10 1 9 2 10 1 9 3 10 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 226 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT DISTINCT a FROM t WHERE a = 1 AND (b = 2 OR b = 15); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 10 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select distinct `test`.`t`.`a` AS `a` from `test`.`t` where ((`test`.`t`.`a` = 1) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15))) FLUSH STATUS; SELECT DISTINCT a FROM t WHERE a = 1 AND (b = 2 OR b = 15); a 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 3 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE a = 1 AND (c = 3 OR c = 40) AND (d = 1 OR d = 9) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` = 1) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` = 1) or (`test`.`t`.`d` = 9))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE a = 1 AND (c = 3 OR c = 40) AND (d = 1 OR d = 9) GROUP BY a, b; a MAX(d) MIN(d) 1 9 1 1 9 1 1 9 1 1 9 1 1 9 1 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 26 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 2 10 1 12 15 -15 -20 12 2 -23 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 19 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a < 2 OR a > 11) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`a` < 2) or (`test`.`t`.`a` > 11)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a < 2 OR a > 11) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 2 10 1 12 15 -15 -20 12 2 -23 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 33 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT DISTINCT a, b FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select distinct `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) FLUSH STATUS; SELECT DISTINCT a, b FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40); a b 1 2 12 15 12 2 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 10 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT DISTINCT a FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 10 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select distinct `test`.`t`.`a` AS `a` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15))) FLUSH STATUS; SELECT DISTINCT a FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15); a 1 12 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 6 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a = 12) AND (b < 4 OR b > 10) AND (c = 3 OR c = 40) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 8 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` < 4) or (`test`.`t`.`b` > 10)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a = 12) AND (b < 4 OR b > 10) AND (c = 3 OR c = 40) GROUP BY a, b; a b MAX(d) MIN(d) 1 1 10 1 1 2 10 1 1 3 10 1 12 15 -15 -20 12 2 -23 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 27 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a > 9) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 8 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` > 9)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (a = 1 OR a > 9) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40) GROUP BY a; a MAX(d) MIN(d) 1 10 1 10 10 1 11 -10 -10 12 -15 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 30 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT COUNT(DISTINCT a, b) FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 2 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select count(distinct `test`.`t`.`a`,`test`.`t`.`b`) AS `COUNT(DISTINCT a, b)` from `test`.`t` where (((`test`.`t`.`a` = 1) or (`test`.`t`.`a` = 12)) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) FLUSH STATUS; SELECT COUNT(DISTINCT a, b) FROM t WHERE (a = 1 OR a = 12) AND (b = 2 OR b = 15) AND (c = 3 OR c = 40); COUNT(DISTINCT a, b) 3 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 10 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT SUM(DISTINCT a), AVG(DISTINCT a) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select sum(distinct `test`.`t`.`a`) AS `SUM(DISTINCT a)`,avg(distinct `test`.`t`.`a`) AS `AVG(DISTINCT a)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) FLUSH STATUS; SELECT SUM(DISTINCT a), AVG(DISTINCT a) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40); SUM(DISTINCT a) AVG(DISTINCT a) 78 6.5000 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 24 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT SUM(DISTINCT a), AVG(DISTINCT a), COUNT(DISTINCT a) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40); id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 15 NULL 52 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select sum(distinct `test`.`t`.`a`) AS `SUM(DISTINCT a)`,avg(distinct `test`.`t`.`a`) AS `AVG(DISTINCT a)`,count(distinct `test`.`t`.`a`) AS `COUNT(DISTINCT a)` from `test`.`t` where (((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40))) FLUSH STATUS; SELECT SUM(DISTINCT a), AVG(DISTINCT a), COUNT(DISTINCT a) FROM t WHERE (b = 2 OR b = 15) AND (c = 3 OR c = 40); SUM(DISTINCT a) AVG(DISTINCT a) COUNT(DISTINCT a) 78 6.5000 12 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 24 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MAX(d), MIN(d) FROM t WHERE (a > 11) AND (b = 2 OR b = 15) AND (c = 3 OR c = 1 OR c = 2 OR c = 4) GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 5 NULL 13 6.53 Using where; Using index Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 11) and ((`test`.`t`.`b` = 2) or (`test`.`t`.`b` = 15)) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 1) or (`test`.`t`.`c` = 2) or (`test`.`t`.`c` = 4))) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MAX(d), MIN(d) FROM t WHERE (a > 11) AND (b = 2 OR b = 15) AND (c = 3 OR c = 1 OR c = 2 OR c = 4) GROUP BY a; a MAX(d) MIN(d) 12 -15 -15 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 1 Handler_read_last 0 Handler_read_next 4 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (c = 3 OR c = 40) AND (d > 7 OR d =2) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 112 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` > 7) or (`test`.`t`.`d` = 2))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (c = 3 OR c = 40) AND (d > 7 OR d =2) GROUP BY a, b; a b MAX(d) MIN(d) 1 1 10 2 1 2 10 2 1 3 10 2 1 4 10 2 1 5 10 2 10 1 10 2 10 2 10 2 10 3 10 2 10 4 10 2 10 5 10 2 11 3 20 20 11 NULL 30 30 2 1 10 2 2 2 10 2 2 3 10 2 2 4 10 2 2 5 10 2 3 1 10 2 3 2 10 2 3 3 10 2 3 4 10 2 3 5 10 2 4 1 10 2 4 2 10 2 4 3 10 2 4 4 10 2 4 5 10 2 5 1 10 2 5 2 10 2 5 3 10 2 5 4 10 2 5 5 10 2 6 1 10 2 6 2 10 2 6 3 10 2 6 4 10 2 6 5 10 2 7 1 10 2 7 2 10 2 7 3 10 2 7 4 10 2 7 5 10 2 8 1 10 2 8 2 10 2 8 3 10 2 8 4 10 2 8 5 10 2 9 1 10 2 9 2 10 2 9 3 10 2 9 4 10 2 9 5 10 2 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 281 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (c = 3 OR c = 40) AND (d > 7 OR d =2 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 112 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where (((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` > 7) or (`test`.`t`.`d` = 2) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (c = 3 OR c = 40) AND (d > 7 OR d =2 OR d IS NULL) GROUP BY a, b; a b MAX(d) MIN(d) 1 1 10 2 1 2 10 2 1 3 10 2 1 4 10 2 1 5 10 2 10 1 10 2 10 2 10 2 10 3 10 2 10 4 10 2 10 5 10 2 11 3 20 20 11 NULL 30 30 12 2 NULL NULL 2 1 10 2 2 2 10 2 2 3 10 2 2 4 10 2 2 5 10 2 3 1 10 2 3 2 10 2 3 3 10 2 3 4 10 2 3 5 10 2 4 1 10 2 4 2 10 2 4 3 10 2 4 4 10 2 4 5 10 2 5 1 10 2 5 2 10 2 5 3 10 2 5 4 10 2 5 5 10 2 6 1 10 2 6 2 10 2 6 3 10 2 6 4 10 2 6 5 10 2 7 1 10 2 7 2 10 2 7 3 10 2 7 4 10 2 7 5 10 2 8 1 10 2 8 2 10 2 8 3 10 2 8 4 10 2 8 5 10 2 9 1 10 2 9 2 10 2 9 3 10 2 9 4 10 2 9 5 10 2 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 1 Handler_read_key 396 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MIN(d) FROM t WHERE (a > 5) AND (c = 3 OR c = 40) AND (d < -24 OR d = 3 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 56 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 5) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` < (-(24))) or (`test`.`t`.`d` = 3) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MIN(d) FROM t WHERE (a > 5) AND (c = 3 OR c = 40) AND (d < -24 OR d = 3 OR d IS NULL) GROUP BY a, b; a b MIN(d) 10 1 3 10 2 3 10 3 3 10 4 3 10 5 3 12 2 NULL 6 1 3 6 2 3 6 3 3 6 4 3 6 5 3 7 1 3 7 2 3 7 3 3 7 4 3 7 5 3 8 1 3 8 2 3 8 3 3 8 4 3 8 5 3 9 1 3 9 2 3 9 3 3 9 4 3 9 5 3 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 154 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a > 5) AND (c = 3 OR c = 40) AND (d < 1 OR d = 9) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 56 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 5) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` < 1) or (`test`.`t`.`d` = 9))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a > 5) AND (c = 3 OR c = 40) AND (d < 1 OR d = 9) GROUP BY a, b; a b MAX(d) MIN(d) 10 1 9 9 10 2 9 9 10 3 9 9 10 4 9 9 10 5 9 9 11 2 -10 -10 12 15 -15 -20 12 2 -23 -23 6 1 9 9 6 2 9 9 6 3 9 9 6 4 9 9 6 5 9 9 7 1 9 9 7 2 9 9 7 3 9 9 7 4 9 9 7 5 9 9 8 1 9 9 8 2 9 9 8 3 9 9 8 4 9 9 8 5 9 9 9 1 9 9 9 2 9 9 9 3 9 9 9 4 9 9 9 5 9 9 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 185 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d = 1 OR d = 9 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `MAX(d)`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 9) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` = 1) or (`test`.`t`.`d` = 9) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MAX(d), MIN(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d = 1 OR d = 9 OR d IS NULL) GROUP BY a, b; a b MAX(d) MIN(d) 10 1 9 1 10 2 9 1 10 3 9 1 10 4 9 1 10 5 9 1 12 2 NULL NULL SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 80 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d = 0 OR d = 9 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `MIN(d)`,max(`test`.`t`.`d`) AS `MAX(d)` from `test`.`t` where ((`test`.`t`.`a` > 9) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` = 0) or (`test`.`t`.`d` = 9) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d = 0 OR d = 9 OR d IS NULL) GROUP BY a, b; a b MIN(d) MAX(d) 10 1 9 9 10 2 9 9 10 3 9 9 10 4 9 9 10 5 9 9 12 2 NULL NULL SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 85 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d > 0 AND d < 2 OR d > 3 AND d < 5 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `MIN(d)`,max(`test`.`t`.`d`) AS `MAX(d)` from `test`.`t` where ((`test`.`t`.`a` > 9) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and (((`test`.`t`.`d` > 0) and (`test`.`t`.`d` < 2)) or ((`test`.`t`.`d` > 3) and (`test`.`t`.`d` < 5)) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MIN(d), MAX(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d > 0 AND d < 2 OR d > 3 AND d < 5 OR d IS NULL) GROUP BY a, b; a b MIN(d) MAX(d) 10 1 1 4 10 2 1 4 10 3 1 4 10 4 1 4 10 5 1 4 12 2 NULL NULL SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 79 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b, MIN(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d < 2 OR d > 5 OR d IS NULL) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `MIN(d)` from `test`.`t` where ((`test`.`t`.`a` > 9) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` < 2) or (`test`.`t`.`d` > 5) or (`test`.`t`.`d` is null))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b, MIN(d) FROM t WHERE (a > 9) AND (c = 3 OR c = 40) AND (d < 2 OR d > 5 OR d IS NULL) GROUP BY a, b; a b MIN(d) 10 1 1 10 2 1 10 3 1 10 4 1 10 5 1 11 2 -10 11 3 20 11 NULL 30 12 15 -20 12 2 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 46 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b , max(d) FROM t WHERE (a >= 10) AND (c = 3 or c=40) AND (d is NULL or d = -10 or d = -23) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,max(`test`.`t`.`d`) AS `max(d)` from `test`.`t` where ((`test`.`t`.`a` >= 10) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` is null) or (`test`.`t`.`d` = (-(10))) or (`test`.`t`.`d` = (-(23))))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b , max(d) FROM t WHERE (a >= 10) AND (c = 3 or c=40) AND (d is NULL or d = -10 or d = -23) GROUP BY a, b; a b max(d) 11 2 -10 12 2 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 74 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, b , min(d) FROM t WHERE (a >= 10) AND (c = 3 or c=40) AND (d is NULL or d = -10 or d = -23) GROUP BY a, b; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 20 NULL 12 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,`test`.`t`.`b` AS `b`,min(`test`.`t`.`d`) AS `min(d)` from `test`.`t` where ((`test`.`t`.`a` >= 10) and ((`test`.`t`.`c` = 3) or (`test`.`t`.`c` = 40)) and ((`test`.`t`.`d` is null) or (`test`.`t`.`d` = (-(10))) or (`test`.`t`.`d` = (-(23))))) group by `test`.`t`.`a`,`test`.`t`.`b` FLUSH STATUS; SELECT a, b , min(d) FROM t WHERE (a >= 10) AND (c = 3 or c=40) AND (d is NULL or d = -10 or d = -23) GROUP BY a, b; a b min(d) 11 2 -10 12 2 -23 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 76 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; EXPLAIN SELECT a, MIN(b), MAX(b) FROM t WHERE a > 9 GROUP BY a; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t NULL range k1 k1 10 NULL 1 100.00 Using where; Using index for group-by Warnings: Note 1003 /* select#1 */ select `test`.`t`.`a` AS `a`,min(`test`.`t`.`b`) AS `MIN(b)`,max(`test`.`t`.`b`) AS `MAX(b)` from `test`.`t` where (`test`.`t`.`a` > 9) group by `test`.`t`.`a` FLUSH STATUS; SELECT a, MIN(b), MAX(b) FROM t WHERE a > 9 GROUP BY a; a MIN(b) MAX(b) 10 1 5 11 2 15 12 2 15 SHOW STATUS LIKE 'handler_read%'; Variable_name Value Handler_read_first 0 Handler_read_key 8 Handler_read_last 1 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 0 include/diff_tables.inc [test.group_query, test.no_group_query] drop tables test.group_query, test.no_group_query; DROP TABLE t;