Harrison,
Taking your suggestion and building a combined key of member_id
and pts_awarded the query took 17 mins
create table pts_sumC_snap
select member_id, count(1) count, sum(pts_awarded) points
from pts_awarded_snap
group by member_id;
Query OK, 12488780 rows affected (16 min 50.21 sec)
Records: 12488780 Duplicates: 0 Warnings: 0
Building the combined index took 1 hr 12 mins for the total creation
time of approximately 1.5 hours.
Without any kind of index on the pts_awarded_snap table the
query took 7 hours to build a similar summation table.
When I built the index on member_id, the query took 31 hours to complete
utilizing the index that took more time to build.
This data set holds 776723372 rows.
Bottom line, there appears to quite a difference between how fast indicies
can be read and processed than how long it takes to process the index
and data combination.
Brad Eacker ([EMAIL PROTECTED])
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
