mysql - Collapse large database table to one row per user -

mysql - Collapse large database table to one row per user -

i running casino (not real money) website everytime user plays row generated on table "trans" on database create database grow soo much crashing server

this table structure

name type collation attributes null default action 1 user_id int(11) no none alter alter drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 2 amount decimal(16,8) no none alter alter drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 3 aff int(11) no 0 alter alter drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 4 jackpot int(11) no 0 alter alter drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 5 paidout int(11) no 0 alter alter drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 6 type int(11) no 0 alter alter drop drop browse distinct values browse distinct values primary primary unique unique show more actions more 7 created timestamp no current_timestamp alter alter drop drop browse distinct values browse distinct values primary primary unique unique show more actions more

this table has abount 4 1000000 rows , 30000 users (user_id ) question , there way to collapse table allow daily , save 1 row per user , maintain balances (amount).

i trying without success:

update trans

select * trans grouping user_id set amount = (select sum(amount) trans

here broad steps:

create new summary table. add trigger on original table user , add together amount total in summary table clear out old entries on schedule

or

change application logic update instead of insert

or

you can write 1 single grouping style query add together users info , homecoming instead of detail - doing select * using memory.. bad

mysql sql database