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 :

  1. 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
  2. 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
  3. 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.

Cheers, Roberto

  • How do you plan to write the queries in option 2 or 3, when there are 100s of tables or databases? Even a simple query like SELECT MAX(col_5) FROM table, it will be much more complex. – ypercubeᵀᴹ Oct 20 '15 at 15:35
  • Will you be purging "old" data? Or do you plan to keep all data forever? – Rick James Oct 20 '15 at 17:42
  • Show us SHOW CREATE TABLE for the table you build from the CSV file(s). Show us the SELECTs you will be doing. Those need to be focused on first. – Rick James Oct 20 '15 at 17:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.