Re: Consumption of more hard disk space in "Copying to tmp table" phase

[Raghavendra K]

Hi Mattias,

Thanks & Appreciate response!

I'm using MySQL 5.5.10

After removing ORDER BY there is no "Copying to tmp tables" phase, query execution went with "Sending data". Hard disk space used was around 25MB. ORBER BY should be executed on the final result right?

How to get to know in which subpart of the query execution ORDER BY is getting invoked? below is the explain select

mysql> explain SELECT `evt`.`time`, `mdl`.`mdl_name`, `mdl`.`address`, `creator`.`creator_name`, `evt`.`type`, `evt`.`evt_key`, `landscape`.`user` FROM ((`rpt`.`evt` `evt` INNER JOIN `rpt`.`mdl` `mdl` ON `evt`.`mdl_key`=`mdl`.`mdl_key`) INNER JOIN `rpt`.`creator` `creator` ON `evt`.`cid`=`creator`.`cID`) INNER JOIN `rpt`.`landscape` `landscape` ON (`mdl`.`mdlhandle`=`landscape`.`handle`) AND (`mdl`.`secstring`=`landscape`.`secstring`) WHERE (`evt`.`time`>='2013-02-01 01:00:00' AND `evt`.`time`<'2013-02-28 23:59:01') AND `landscape`.`user`='Administrator' ORDER BY `evt`.`time` DESC;
+----+-------------+---------+-------------+-----------------------------------------------------------------------------------------------------------------------------+-----------------------------------------+--------
-+---------------------------------------+-------+-----------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len
| ref | rows | Extra |
+----+-------------+---------+-------------+-----------------------------------------------------------------------------------------------------------------------------+-----------------------------------------+--------
-+---------------------------------------+-------+-----------------------------------------------------------------------+
| 1 | SIMPLE | u | const | unq_user | unq_user | 768
| const | 1 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | ou | const | PRIMARY,unq_user | unq_user | 768
| const | 1 | Using index |
| 1 | SIMPLE | m | index_merge | idx_mdl_mtype_h,idx_mdl_mdl_class,landscape_h_idx,idx_mdl_name,idx_mdl_destroy_time,idx_mdl_landscape_secstring | idx_mdl_mtype_h,idx_mdl_mdl_class | 5,5
| NULL | 1 | Using intersect(idx_mdl_mtype_h,idx_mdl_mdl_class); Using where |
| 1 | SIMPLE | mdl | ref | PRIMARY,landscape_h_idx,idx_mdl_secstring,idx_mdl_landscape_secstring | landscape_h_idx | 5
| rpt.m.landscape_h | 61990 | Using where |
| 1 | SIMPLE | ss | ref | PRIMARY,unq_securiting_string | unq_securiting_string | 768
| rpt.mdl.security_string | 1 | Using where; Using index |
| 1 | SIMPLE | ssa | ref | unq_user | unq_user | 10
| const,rpt.ss.security_string_id | 1 | Using where; Using index |
| 1 | SIMPLE | evt | ref | mdl_key_idx,time_idx,creator_id_idx | mdl_key_idx | 4
| rpt.mdl.mdl_key | 29 | Using where |
| 1 | SIMPLE | creator | eq_ref | PRIMARY | PRIMARY | 4
| rpt.evt.creator_id | 1 | |
+----+-------------+---------+-------------+-----------------------------------------------------------------------------------------------------------------------------+-----------------------------------------+--------
-+---------------------------------------+-------+-----------------------------------------------------------------------+
8 rows in set (0.25 sec)

-Raghu