MySQL Forums
Forum List  »  Partitioning

Re: Consumption of more hard disk space in "Copying to tmp table" phase
Posted by: Raghavendra K
Date: April 16, 2014 06:40AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Consumption of more hard disk space in "Copying to tmp table" phase
1179
April 16, 2014 06:40AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.