CREATE TABLE tbl1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, str1 VARCHAR(11) NOT NULL, str2 VARCHAR(11) NOT NULL, KEY (str1, str2)) ENGINE=MyISAM; INSERT INTO tbl1(id) VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); INSERT INTO tbl1(id) SELECT NULL FROM tbl1; INSERT INTO tbl1(id) SELECT NULL FROM tbl1; INSERT INTO tbl1(id) SELECT NULL FROM tbl1; INSERT INTO tbl1(id) SELECT NULL FROM tbl1; INSERT INTO tbl1(id) SELECT NULL FROM tbl1; INSERT INTO tbl1(id) SELECT NULL FROM tbl1; INSERT INTO tbl1(id) SELECT NULL FROM tbl1; INSERT INTO tbl1(id) SELECT NULL FROM tbl1; INSERT INTO tbl1(id) SELECT NULL FROM tbl1; INSERT INTO tbl1(id) SELECT NULL FROM tbl1; INSERT INTO tbl1(id) SELECT NULL FROM tbl1; INSERT INTO tbl1(id) SELECT NULL FROM tbl1; INSERT INTO tbl1(id) SELECT NULL FROM tbl1; INSERT INTO tbl1(id) SELECT NULL FROM tbl1; INSERT INTO tbl1(id) SELECT NULL FROM tbl1; INSERT INTO tbl1(id) SELECT NULL FROM tbl1; INSERT INTO tbl1(id) SELECT NULL FROM tbl1; UPDATE tbl1 SET str1=id%250000, str2=id%120000; CREATE TABLE tbl2 (str1 VARCHAR(11) NOT NULL, str3 CHAR(2) NOT NULL, KEY (str1,str3)); INSERT INTO tbl2 SELECT str1, id%90 FROM tbl1 WHERE id%2; mysql [localhost] {msandbox} (test) > \P egrep -v "\|" mysql [localhost] {msandbox} (test) > SELECT SQL_NO_CACHE COUNT(*) FROM tbl1 , tbl2 WHERE tbl1.str1 = tbl2.str1 GROUP BY tbl2.str3, tbl1.str2 WITH ROLLUP HAVING str2 IS NOT NULL AND str3 IS NOT NULL; +----------+ +----------+ +----------+ 540000 rows in set (50.16 sec) mysql [localhost] {msandbox} (test) > SELECT SQL_NO_CACHE COUNT(*) FROM tbl1 , tbl2 WHERE tbl1.str1 = tbl2.str1 GROUP BY tbl2.str3, tbl1.str2 HAVING str2 IS NOT NULL AND str3 IS NOT NULL; +----------+ +----------+ +----------+ 540000 rows in set (1 min 50.48 sec) mysql [localhost] {msandbox} (test) > \P mysql [localhost] {msandbox} (test) > EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM tbl1 , tbl2 WHERE tbl1.str1 = tbl2.str1 GROUP BY tbl2.str3, tbl1.str2 WITH ROLLUP HAVING str2 IS NOT NULL AND str3 IS NOT NULL; +----+-------------+-------+-------+---------------+------+---------+----------------+---------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+----------------+---------+----------------------------------------------+ | 1 | SIMPLE | tbl1 | index | str1 | str1 | 26 | NULL | 1048576 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | tbl2 | ref | str1 | str1 | 13 | test.tbl1.str1 | 4 | Using index | +----+-------------+-------+-------+---------------+------+---------+----------------+---------+----------------------------------------------+ 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > EXPLAIN SELECT SQL_NO_CACHE COUNT(*) FROM tbl1 , tbl2 WHERE tbl1.str1 = tbl2.str1 GROUP BY tbl2.str3, tbl1.str2 HAVING str2 IS NOT NULL AND str3 IS NOT NULL; +----+-------------+-------+-------+---------------+------+---------+----------------+---------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+----------------+---------+----------------------------------------------+ | 1 | SIMPLE | tbl1 | index | str1 | str1 | 26 | NULL | 1048576 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | tbl2 | ref | str1 | str1 | 13 | test.tbl1.str1 | 4 | Using index | +----+-------------+-------+-------+---------------+------+---------+----------------+---------+----------------------------------------------+ 2 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > SHOW VARIABLES LIKE 'max_heap_table_size'\G SHOW VARIABLES LIKE 'tmp_table_size'\G *************************** 1. row *************************** Variable_name: max_heap_table_size Value: 16777216 1 row in set (0.00 sec) *************************** 1. row *************************** Variable_name: tmp_table_size Value: 16777216 1 row in set (0.00 sec) ------------------ mysql [localhost] {msandbox} (test) > SET max_heap_table_size=64*1024*1024; SET tmp_table_size=64*1024*1024; mysql [localhost] {msandbox} (test) > \P egrep -v "\|" mysql [localhost] {msandbox} (test) > SELECT SQL_NO_CACHE COUNT(*) FROM tbl1 , tbl2 WHERE tbl1.str1 = tbl2.str1 GROUP BY tbl2.str3, tbl1.str2 HAVING str2 IS NOT NULL AND str3 IS NOT NULL; +----------+ +----------+ +----------+ 540000 rows in set (49.13 sec) mysql [localhost] {msandbox} (test) > SELECT SQL_NO_CACHE COUNT(*) FROM tbl1 , tbl2 WHERE tbl1.str1 = tbl2.str1 GROUP BY tbl2.str3, tbl1.str2 WITH ROLLUP HAVING str2 IS NOT NULL AND str3 IS NOT NULL; +----------+ +----------+ +----------+ 540000 rows in set (47.24 sec) mysql [localhost] {msandbox} (test) > SET max_heap_table_size=16*1024*1024; SET tmp_table_size=16*1024*1024; mysql [localhost] {msandbox} (test) > SET profiling=1; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (test) > SELECT SQL_NO_CACHE COUNT(*) FROM tbl1 , tbl2 WHERE tbl1.str1 = tbl2.str1 GROUP BY tbl2.str3, tbl1.str2 WITH ROLLUP HAVING str2 IS NOT NULL AND str3 IS NOT NULL; +----------+ +----------+ +----------+ 540000 rows in set (49.34 sec) mysql [localhost] {msandbox} (test) > \P Default pager wasn't set, using stdout. mysql [localhost] {msandbox} (test) > SHOW PROFILE; +------------------------------+-----------+ | Status | Duration | +------------------------------+-----------+ | starting | 0.000207 | | Opening tables | 0.000024 | | System lock | 0.000011 | | Table lock | 0.000014 | | init | 0.000058 | | optimizing | 0.000023 | | statistics | 0.000043 | | preparing | 0.000024 | | Creating tmp table | 0.000052 | | executing | 0.000006 | | Copying to tmp table | 16.184914 | | converting HEAP to MyISAM | 0.185012 | | Copying to tmp table on disk | 26.962789 | | Sorting result | 4.847789 | | Sending data | 1.138066 | | end | 0.000011 | | removing tmp table | 0.011800 | | end | 0.000013 | | query end | 0.000005 | | freeing items | 0.003404 | | logging slow query | 0.000009 | | logging slow query | 0.000003 | | cleaning up | 0.000006 | +------------------------------+-----------+ 23 rows in set (0.00 sec) mysql [localhost] {msandbox} (test) > \P egrep -v "\|" mysql [localhost] {msandbox} (test) > SELECT SQL_NO_CACHE COUNT(*) FROM tbl1 , tbl2 WHERE tbl1.str1 = tbl2.str1 GROUP BY tbl2.str3, tbl1.str2 HAVING str2 IS NOT NULL AND str3 IS NOT NULL; +----------+ +----------+ +----------+ 540000 rows in set (1 min 49.50 sec) mysql [localhost] {msandbox} (test) > \P mysql [localhost] {msandbox} (test) > SHOW PROFILE; +------------------------------+-----------+ | Status | Duration | +------------------------------+-----------+ | starting | 0.000153 | | Opening tables | 0.000025 | | System lock | 0.000011 | | Table lock | 0.000016 | | init | 0.000055 | | optimizing | 0.000025 | | statistics | 0.000045 | | preparing | 0.000025 | | Creating tmp table | 0.000057 | | executing | 0.000005 | | Copying to tmp table | 21.311544 | | converting HEAP to MyISAM | 15.049406 | | Copying to tmp table on disk | 71.897174 | | Sorting result | 0.828482 | | Sending data | 0.397294 | | end | 0.000010 | | removing tmp table | 0.012662 | | end | 0.000013 | | query end | 0.000005 | | freeing items | 0.004858 | | logging slow query | 0.000009 | | logging slow query | 0.000003 | | cleaning up | 0.000007 | +------------------------------+-----------+ 23 rows in set (0.00 sec)