We are using MySql.
I have a large number of tables with millions of rows each. Those tables are updated every second and are used both for adding info and retrieving info. Each table can be 5GB or 10GB or even more.
I have one table that I keep sums of information (something like a summary table of the information I need) but this is starting to get big in size as well.
at the moment I cannot change database due to various reasons (mainly no knowledge, time and budget)
all the extra power that we add to the server goes to other resources needed so I cannot run very heavy queries
Temporary ways I have thought for scaling
Having these things in mind I am trying to think of ways to scale with what I have:
For the tables with millions of rows I have thought to keep to separate databases (could make my life easier for backups / exports / changes). Keep my main data in 1 database and all peripherals (huge tables) to other databases. Let’s say have a different database for a different need.
For the problem with the table that I really need regularly and is growing fast I was thinking into splitting it into XX tables. Could be 1 table per user (which might be too much) or 1 table per XXX users.
Are these ideas totally crazy and really bad DB design?
If yes..... any suggestions other than changing everything at once?