When indexes are created in internal temporary tables
During my previous post on how to improve derived tables performance, I patched the code to add indexes forcefully on internal derived table results, which made a huge difference in the performance. It was just an experiment and a thought to see if it really works without re-writing the queries, so that the logic can be pushed towards the engine rather than query re-write. \
But I got few emails in my inbox today asking whether MySQL really create any keys on internal temporary tables.
The answer is YES; and MySQL does create two keys on internal temporary tables namely ‘group_key‘ and ‘distinct_key‘ on the following conditions:
- If there is any aggregate function and/or group-by (group_key)
- Distinct column name(group_key)
- Distinct in combination with group-by/aggregation functions (distinct_key)
Provided the query results are yielded in temporary table (Using temporary from the explain), else they get optimized away by the existing indexes from the regular table itself. These keys are added to both memory and disk based (MyISAM) internal temporary tables; so it does not matter if the internal temporary table is in memory or disk.
Here is a simple dump of internal temporary table index stats for some of the basic queries related to Information schema [Warning: these queries are really bad, and can't be used for any production use as they are meant for demonstration of different internal keys ]. This is a patch that I might be using for SHOW TEMPORARY TABLES when internal tables are included in the second version. The first version of the patch is already pushed to Maria branch, hoping that it gets pushed to 5.1.
----------------------------- TMP TABLE STATS, SESSION: 1 temp file : /tmp/#sqlf90_1_1f temp type : MEMORY index count: 1 key 1-1 : distinct_key field : (null) key 1-2 : distinct_key field : ENGINE query: select count(distinct engine) from information_schema.tables ----------------------------- ----------------------------- TMP TABLE STATS, SESSION: 1 temp file : /tmp/#sqlf90_1_21 temp type : MEMORY index count: 1 key 1-1 : group_key field : TABLE_NAME query: select table_name, sum(data_length+index_length) from information_schema.tables where table_schema='mysql' group by 1 ----------------------------- ----------------------------- TMP TABLE STATS, SESSION: 1 temp file : /tmp/#sqlf90_1_24 temp type : MEMORY index count: 1 key 1-1 : group_key field : TABLE_SCHEMA key 1-2 : group_key field : TABLE_NAME key 1-3 : group_key field : COLUMN_NAME query: select tab.table_schema, tab.table_name, column_name, index_name, seq_in_index from Information_schema.tables tab join information_schema.statistics stast using(table_schema,table_name) group by 1,2,3 ----------------------------- ----------------------------- TMP TABLE STATS, SESSION: 1 temp file : /tmp/#sqlf90_1_bd temp type : MEMORY index count: 1 key 1-1 : group_key field : TABLE_NAME key 1-2 : group_key field : TABLE_TYPE key 1-3 : group_key field : ENGINE key 1-4 : group_key field : INDEX_SCHEMA key 1-5 : group_key field : INDEX_NAME query: select tab.table_schema, tab.table_name, table_type, engine, index_schema, index_name from information_schema.tables tab join information_schema.statistics stats using(table_schema, table_name) where table_schema='mysql' group by 1,2,3,4,5, 6 order by 4,3,2,1 ----------------------------- ----------------------------- TMP TABLE STATS, SESSION: 1 temp file : /tmp/#sqlf90_1_e0 temp type : MEMORY index count: 1 key 1-1 : group_key field : TABLE_NAME query: select table_name, sum(data_length+index_length) from information_schema.tables where table_schema='mysql' group by 1 ----------------------------- |
Related posts:
2 Comments
This is great information!
Comment :: March 8, 2010 @ 2:28 pm
Thanks for the explanation. Do know when can we expect show temporary tables patch? Or you should publish a plugin
Comment :: March 8, 2010 @ 2:41 pm