I must have done a poor job at explaining this. I already have all those primary keys, etc. If you are required to do a full table scan on the table, as I am in my example, please explain to me how an index even matters in that case. There is _no_ "where" clause in the query so how will an index help? :)
Now I have been benchmarking creating a CKEY "combined key" column that just basically is a concat(month_day, ',', bucket, ',', src). I made that a char(44) column and make that the primary key. Things seem faster due to use only one row for the primary key instead of 3, as well as a less complex "group by". Preliminary results show the CKEY to be 50% faster on the particular query I'm using. 1) Yes, it does have to calculate floor(bucket / 3) but that is fairly in expensive call on a per-row basis, one would think. 2) I already have that index. It's a Primary Key(month_day, src, bucket). I'm still confused on how the index will speed it up on the source table side. We are NOT I/O bound. Looks more like cpu bound to me. Mysql uses 25% cpu on the solaris which is 1 entire cpu on a 4 processor machine. I'll give the order by NULL a shot Cliff Jigal van Hemert <[EMAIL PROTECTED]> writes: > > 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]
