I am looking into a project which involves large number of inserts daily.I will have a list of user's (for example a user set of 500k ) , for which I need to monitor daily certain activities associated with them.
For example, let there be a set of 100 users say U1,U2,...,U100
I need to insert their daily scores into my database.
Consider the total score obtained for a user U1 for period June 30 - July 6, is as follows
June 30 - 99 July 1 - 100 July 2 - 102 July 3 - 102 July 4 - 105 July 5 - 105 July 6 - 107
The database should keep daily scores of each users ,like
For user U1,
July 1- 1pt (100-99) July 2- 2pt (102-100) July 3- 0pt (102-102) July 4- 3pt (105-102) July 5- 0pt (105-105) July 6- 2pt (107-105)
Similarly the database should hold daily details of the full set of user's.
And on a later phase , I envision to take aggregate reports out of these data like total points scored on each day,week,month,etc; and to compare it with older data.
I need to start things from the scratch.I am experienced with PHP as a server side script and MYSQL. I am confused on the database side ? Since I need to process about a million insertion daily,what all things should be taken care of ?
Does MySQL fits my requirement,If so what storage engine should be used ? Initially I envision to create a user table with a foreign key user id and monthly score tables with dates as fields.And later on I got suggestion to write things into a csv/excel first and then to load them to table after a particular period.
Does file insertion makes things more favorable in this regard.
Or should I try for some other databases,NoSQL methods ?
I need to maintain this data warehouse and aggregate these info to weekly and monthly reports.In some earlier case I found the MySQL queries are taking much time in execution.
Any help will be highly appreciated. Thanks in advance.