Details
-
Task
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
None
Description
DROP TABLE IF EXISTS t1;
|
CREATE TABLE t1 (id INT NOT NULL, a INT NOT NULL) ENGINE=HEAP;
|
INSERT INTO t1 VALUES (1, 1);
|
INSERT INTO t1 VALUES (1, 2);
|
INSERT INTO t1 VALUES (1, 3);
|
INSERT INTO t1 VALUES (1, 4);
|
INSERT INTO t1 VALUES (2, 1);
|
INSERT INTO t1 VALUES (2, 2);
|
INSERT INTO t1 VALUES (2, 3);
|
INSERT INTO t1 VALUES (2, 4);
|
INSERT INTO t1 VALUES (3, 1);
|
INSERT INTO t1 VALUES (3, 2);
|
INSERT INTO t1 VALUES (3, 3);
|
INSERT INTO t1 VALUES (3, 4);
|
INSERT INTO t1 VALUES (4, 1);
|
INSERT INTO t1 VALUES (4, 2);
|
INSERT INTO t1 VALUES (4, 3);
|
INSERT INTO t1 VALUES (4, 4);
|
ALTER TABLE t1 ADD KEY (id,a) USING BTREE;
|
EXPLAIN SELECT id,MIN(a) FROM t1 WHERE a=4 GROUP BY id;
|
EXPLAIN SELECT id,MIN(a) FROM t1 WHERE a>=4 GROUP BY id;
|
Both EXPLAIN return
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | index | NULL | id | 8 | NULL | 16 | Using where |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|
which means group-min-max is not used.
Perhaps it could in both cases.
Notice, USING BTREE makes the index suitable for ">=".