I was experimenting with text and varchars in mysql 5.7. I create a simple mysql table as below
CREATE TABLEsmall_table
(pk
int(11) NOT NULL AUTO_INCREMENT,varc
varchar(255) DEFAULT NULL,txt
text, PRIMARY KEY (pk
) ) ENGINE=InnoDB AUTO_INCREMENT=103925 DEFAULT CHARSET=utf8
The table has around 100k rows.
I have executed the below queries and profiled the both
Query 1 (Group by on varchar column)
select varc,count(*) from small_table group by varc;
Profile 1 result
+----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000064 | | checking permissions | 0.000004 | | Opening tables | 0.004812 | | init | 0.000026 | | System lock | 0.000005 | | optimizing | 0.000006 | | statistics | 0.000010 | | preparing | 0.000006 | | Creating tmp table | 0.000021 | | Sorting result | 0.000003 | | executing | 0.000001 | | Sending data | 0.052795 | | Creating sort index | 0.000049 | | end | 0.000003 | | query end | 0.000006 | | removing tmp table | 0.000004 | | query end | 0.000002 | | closing tables | 0.000006 | | freeing items | 0.000021 | | cleaning up | 0.000018 | +----------------------+----------+
Temp table Details 1
+-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 0 | | Created_tmp_tables | 1 | +-------------------------+-------+
Query 2 (Group by on text column)
select txt,count(*) from small_table group by txt;
Profile 2 result
+----------------------+-----------+ | Status | Duration | +----------------------+-----------+ | starting | 0.000134 | | checking permissions | 0.000010 | | Opening tables | 0.006690 | | init | 0.000034 | | System lock | 0.000007 | | optimizing | 0.000004 | | statistics | 0.000017 | | preparing | 0.000008 | | Creating tmp table | 0.000027 | | Sorting result | 0.000005 | | executing | 0.000002 | | Sending data | 26.359877 | | Creating sort index | 0.001042 | | end | 0.000007 | | query end | 0.000007 | | removing tmp table | 0.000208 | | query end | 0.000003 | | closing tables | 0.000007 | | freeing items | 0.000028 | | cleaning up | 0.000020 | +----------------------+-----------+
Temp table Details 2
+-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 1 | | Created_tmp_tables | 1 | +-------------------------+-------+
Both profiles took almost same time in each stage expect 'Sending data stage'. For first one it took 0.052795s and for second one it took 26.359877s. Why there is that much difference in that stage? (same queries without group by clause show no difference in 'Sending data stage')