I'm creating a project that could get a lot of users very quickly and I wanted to know what is the suggested approach I should consider from the start.
I'm developing it with a PHP framework and MYSQL both optimized as far as my knowledge allows :P I basically want to start small...I mean running on a simple VPS and scale to better hardware as needed but in the end I was thinking that one database's table could be become a real problem. Basically there will be a table that stores activities every user could do 100 times per day (I'm not going into details) so considering that in the future I would have, say, 10k users doing that every day I would get to a point of millions of new data per day (even if it just stores 2-3 numbers). And this table even if it's not queried to retrieve data at every visit but only only to INSERT I would need to read it and manipulate data...and what happens with 1 trillion rows? I'm not an expert but I'm pretty sure that having those numbers in a table cannot be good especially if it grows everyday non-stop.
SO my questions are:
- is there another database method I should start with? Something in front of MYSQL made for large tables? If any!
- how do big web sites handle these things? They store "part" of the rows on multiple servers?
- is it OK to use the simple BIGINT (primary key) for this table considering that is not unlimited? I read that using varchars as primary key slows down the database
- could services like AWS do this for me transparently?