We are a small Internet provider and we are storing all our clients traffic data in a MySQL database (MyISAM). We insert data every few minutes in three tables like this:
capture_table
----------
client_id
date
time
data1
...
datan
---------
The data is always queried by date.
After the tables were getting too big, we decided to use several tables to divide the data. We created three PHP scripts that ran every few minutes dividing the original data into 3 tables (MyISAM) per month and creating an index table.
index_table
data_table_2014-01-1
data_table_2014-01-2
data_table_2014-01-3
...
...
Currently we have this structure and about 2.5 million of rows per table for our current number of clients, and growing (DB weights about 15GB).
The queries are done by joining the data from all the tables involved between two dates using UNION ALL, always searching for date and client. Example, search between 2014-01-01 and 2014-01-15:
(SELECT * FROM data_table_2014-01_1 WHERE client_id=2 AND date >= '2014-01-01' AND date <= '2014-01-15')
UNION ALL (SELECT * FROM data_table_2014-01_2 WHERE client_id=2 AND date >= '2014-01-01' AND date <= '2014-01-15')
UNION ALL (SELECT * FROM data_table_2014-01_3 WHERE client_id=2 AND date >= '2014-01-01' AND date <= '2014-01-15')
First question: Is this the better approach? Could this be achieved in a better way? I was thinking of table partitioning...
Now we are going to move all the database to a new server (virtual), to use this new server only for this kind of data, to liberate the old server and to have a better distribution of the data.
So now we are thinking in using N (virtual) servers as we were doing with the tables (starting development with 2) as a solution for the future. So the application should ask a server_index table to see which server stores that data, and then a table_index table to see which table. And finaly, UNION ALL the data.
As we have not started yet to prepare the servers or the scripts for the migrationexample...
Second question: Could this be achieved in a better way?
Thanks a lot.