MySQL Forums
Forum List  »  Partitioning

Re: Consumption of more hard disk space in "Copying to tmp table" phase
Posted by: Rick James
Date: April 17, 2014 11:36PM

> WHERE (`evt`.`time`>='2013-02-01 01:00:00' AND `evt`.`time`<'2013-02-28 23:59:01')

Some reason for leaving out 1 hour at the beginning of Feb and 59 seconds at the end?

How many rows in the output? Do you need all those rows all at once?

> KEY `lscp_idx` (`handle`),
> KEY `lscp_and_time_idx` (`handle`,`time`),

The former is redundant. The latter can't work, since there is no `handle` field in `evt`.

I see 4 tables in the SELECT, but 8 tables in the EXPLAIN. It is hard to help you if you "simplify" things inconsistently.

The SELECT in the EXPLAIN seems like it would not fetch much more than (61990 * 29) rows. How many rows does it fetch?

If `msg` really is a big field, it may be better to only fetch the PK from evt in the SELECT, then JOIN back to evt again to get the other fields of evt that you need (including msg). (This is a common trick for avoiding huge intermediate tables / disk space / time.

Options: ReplyQuote

Written By
Re: Consumption of more hard disk space in "Copying to tmp table" phase
April 17, 2014 11:36PM

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.