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]

Reply via email to