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

[Rick James]

> 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.