> insert into new_table > select month_day, floor(bucket/3) as bucket, date, src, avg(value) as > value > from source_table > group by month_day, bucket, src; > > Relevant `explain` details: > Full table scan: 22,371,273 rows, Using temporary; Using filesort > > Query OK, 11495208 rows affected (4 hours 47 min 21.01 sec)
IMHO two things slow you down: 1) floor(bucket/3) as bucket. This means that MySQL has to calculate this value for each record to be able to use it as a grouping value. If you always use this same expression for grouping, it might be useful to have a column with the precalculated value. 2) there's only one table involved, so only one index will be used. If you had an index that contains month_day, bucket and src it would speed up things perhaps. Furthermore, it depends on the speed of the disks, your configuration, memory configuration and use how fast it will go. But explain indicates that MySQL needs to copy the data into a temporary table and use filesort to order and group things. Maybe it will help to surpress the automatic sorting that is done by MySQL because of the GROUP BY, by adding ORDER BY NULL? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
