-- 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;

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

You can also specify a MAX_ROWS table option in CREATE TABLE statements for
MEMORY tables to provide a hint about the number of rows you plan to store in
 them. This does not enable the table to grow beyond the max_heap_table_size
 value, which still acts as a constraint on maximum table size. For maximum 
flexibility in being able to use MAX_ROWS, set max_heap_table_size at least as
 high as the value to which you want each MEMORY table to be able to grow.