I am currently working on a project handling a lot of data, and I was wondering which is the best way to store it in a MySQL database.
I am parsing about 150 CSV files, which contain approximately 40000 rows each, for a total of around 6M rows.
Each of these rows has to be stored using a unique ID and the CSV ID, and contains 20 other cells, as such :
unique_id | csv_id | col_1 | ... | col_20
For the moment, I am storing all these rows in just one table in my database, but I'm afraid this is not the best way to store them, and that a simple SELECT query might take forever to be executed.
Moreover, each week, about 10% of this data will be updated, and 10% more will be added, which means that two months from now, I'll have twice as many rows, and so on.
Knowing that, without this table, my database is already about 500MB big, here are some of the solutions I'm considering :
- Use a unique table for all rows (this is the solution established for now)
- Pros : Easy to manage
- Cons : I'm afraid it might be too heavy to handle queries correctly, especially after a few months
- Create one table per CSV file
- Pros : Data won't be too big to handle, even after several months
- Cons : It might be difficult to manage, if and when I'll have to parse new CSV files
- Create and use another database just for these elements, and then using one of the solutions above to store the data
- Pros : This solution will allow my primary database to stay light
- Cons : I don't really know if simultaneously connecting to two different DBs will slow my website more than connecting to a larger DB.
This is the first time I have to manage this much data, which is why I may seem a little confused, but I'm eager for your advice.