Index for group by not working

[Andy Svensson]

Adding indexes for group by optimization doesn't seem to work for heap tables. In the example below I create two tables - a standard MyISAM and a MEMORY one. The MyISAM table can group by far faster than the heap table, even though they're identical and both have BTREE index. Is it intentional that heap tables shouldn't use indexes for group by?

---

-- Setting everything up

SET max_heap_table_size = 104857600;

CREATE TABLE lookup_mem
    (id INT, INDEX USING BTREE (id))
    ENGINE = MEMORY;

CREATE TABLE lookup_hdd
    (id INT, INDEX USING BTREE (id))
    ENGINE = MYISAM;


DELIMITER $$

CREATE PROCEDURE `populate_table`()
BEGIN
    DECLARE count INT DEFAULT 0;
    WHILE count < 3000000 DO
        INSERT INTO lookup_mem SET id = ROUND(RAND()*100);
        SET count = count + 1;
    END WHILE;
END$$

DELIMITER ;



CALL populate_table;

INSERT INTO lookup_hdd SELECT * FROM lookup_mem;

---

Now to the fun stuff:

---

EXPLAIN SELECT * FROM lookup_mem GROUP BY id;

-- gives
-- 1, SIMPLE, lookup_mem, index, , id, 5, , 3000000, 

EXPLAIN SELECT * FROM lookup_hdd GROUP BY id;

-- gives
-- 1, SIMPLE, lookup_hdd, range, , id, 5, , 101, Using index for group-by





SELECT * FROM lookup_mem GROUP BY id;

-- takes 1.216 seconds

SELECT * FROM lookup_hdd GROUP BY id;

-- takes 0.001 seconds

---

I'm using mysql 5.5.29. I've also tried this on 5.1 with the same results.



Edited 1 time(s). Last edit at 01/15/2013 02:31AM by Andy Svensson.