MySQL Forums
Forum List  »  Memory Storage Engine

Index for group by not working
Posted by: Andy Svensson
Date: January 15, 2013 02:20AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Index for group by not working
6152
January 15, 2013 02:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.