Skip to content

Instantly share code, notes, and snippets.

@s0enke

s0enke/gist:2035559

Created Mar 14, 2012
Embed
What would you like to do?
mysql tmp file creation geloet
(debian-sys-maint@localhost[root@www44:mysqld.sock]) [cms]
> SHOW SESSION STATUS like '%Tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 369 |
| Created_tmp_tables | 2 |
+-------------------------+-------+
3 rows in set (0.00 sec)
(debian-sys-maint@localhost[root@www44:mysqld.sock]) [cms]
> explain SELECT image.IMAGE_ID, image.VISIBLE, image.CREATED_TIME, image.CHANGED_TIME, image.DELETED_TIME, image.TYPE_NAME, image.WEBSITE_ID, image.SORT, image.PAYLOAD FROM `image`, module_has_image where image.IMAGE_ID=module_has_image.IMAGE_ID and module_has_image.MODULE_ID=5769090649 AND image.VISIBLE=1 ORDER BY image.SORT ASC;
+----+-------------+------------------+--------+-------------------+-----------+---------+-------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------+--------+-------------------+-----------+---------+-------------------------------+------+----------------------------------------------+
| 1 | SIMPLE | module_has_image | ref | PRIMARY,module_id | module_id | 8 | const | 1 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | image | eq_ref | PRIMARY | PRIMARY | 8 | cms.module_has_image.image_id | 1 | Using where |
+----+-------------+------------------+--------+-------------------+-----------+---------+-------------------------------+------+----------------------------------------------+
2 rows in set (0.00 sec)
(debian-sys-maint@localhost[root@www44:mysqld.sock]) [cms]
> SELECT image.IMAGE_ID, image.VISIBLE, image.CREATED_TIME, image.CHANGED_TIME, image.DELETED_TIME, image.TYPE_NAME, image.WEBSITE_ID, image.SORT, image.PAYLOAD FROM `image`, module_has_image where image.IMAGE_ID=module_has_image.IMAGE_ID and module_has_image.MODULE_ID=5769090649 AND image.VISIBLE=1 ORDER BY image.SORT ASC;
Empty set (0.00 sec)
(debian-sys-maint@localhost[root@www44:mysqld.sock]) [cms]
> SHOW SESSION STATUS like '%Tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1 |
| Created_tmp_files | 369 |
| Created_tmp_tables | 3 |
+-------------------------+-------+
3 rows in set (0.00 sec)
(debian-sys-maint@localhost[root@www44:mysqld.sock]) [cms]
> SET profiling=1;
Query OK, 0 rows affected (0.00 sec)
(debian-sys-maint@localhost[root@www44:mysqld.sock]) [cms]
> SELECT image.IMAGE_ID, image.VISIBLE, image.CREATED_TIME, image.CHANGED_TIME, image.DELETED_TIME, image.TYPE_NAME, image.WEBSITE_ID, image.SORT, image.PAYLOAD FROM `image`, module_has_image where image.IMAGE_ID=module_has_image.IMAGE_ID and module_has_image.MODULE_ID=5769090649 AND image.VISIBLE=1 ORDER BY image.SORT ASC;
Empty set (0.01 sec)
(debian-sys-maint@localhost[root@www44:mysqld.sock]) [cms]
> SHOW PROFILE FOR QUERY 1;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000007 |
| Waiting for query cache lock | 0.000001 |
| Waiting on query cache mutex | 0.000002 |
| checking query cache for query | 0.000034 |
| checking permissions | 0.000002 |
| checking permissions | 0.000002 |
| Opening tables | 0.000009 |
| System lock | 0.000004 |
| Waiting for query cache lock | 0.000001 |
| Waiting on query cache mutex | 0.000004 |
| init | 0.000014 |
| optimizing | 0.000005 |
| statistics | 0.000034 |
| preparing | 0.000011 |
| Creating tmp table | 0.002950 |
| executing | 0.000002 |
| Copying to tmp table | 0.000020 |
| Sorting result | 0.000010 |
| Sending data | 0.000008 |
| end | 0.000001 |
| removing tmp table | 0.000066 |
| end | 0.000002 |
| query end | 0.000002 |
| closing tables | 0.000004 |
| freeing items | 0.000007 |
| logging slow query | 0.000001 |
| cleaning up | 0.000002 |
+--------------------------------+----------+
27 rows in set (0.01 sec)
(debian-sys-maint@localhost[root@www44:mysqld.sock]) [cms]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment