Bug #68086 Heap tables don't use index for group by
Submitted: 15 Jan 2013 8:41 Modified: 15 Jan 2013 19:22
Reporter: Andy Svensson Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Memory storage engine Severity:S3 (Non-critical)
Version:5.5.29, 5.5.30, 5.7.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: group-by, INDEX, optimization

[15 Jan 2013 8:41] Andy Svensson 
Description:
Normally you can add an index to speed group bys up greatly. This doesn't seem to be the case with heap tables. Comparing a standard table with a heap table - both set up with the same structure, data and index - the standard table is far faster because it is using the index for the group by, while the heap table doesn't seem to.

How to repeat:
-- 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;

-- Actual testing
[15 Jan 2013 8:44] Andy Svensson 
Reproduce

Attachment: testing.sql (application/octet-stream, text), 947 bytes.
[15 Jan 2013 8:46] Andy Svensson 
Seems like my submission got sliced.

Anyway, the expected behavior is that the heap tables should use indexes for group bys too, just like ordinary tables.

I added a file for the full test case to reproduce.
[15 Jan 2013 19:22] Sveta Smirnova 
Thank you for the report.

Verified as described.