2

I have a MySQL table that will need to store about 200 million records on a daily basis. The data comes in flat files that I will be bulk uploading using "LOAD DATA INFILE". Most of the data elements are IDs, so not much text. One flat file for a day that consists of 200M records is about 27GB.

My question is should I store all of this information in one table, or should I split each day's data into a separate table. The splitting information would definitely make it harder to analyze, but I am unsure how well MySQL will handle 6 billion records in a single table that would accumulate for a month.

I am currently using an out of the box install for MySQL using MyISAM tables.

(Yes, I am looking to establish a Hadoop solution long term, but wondering if I can make do with MySQL for the short term)

Thanks.

user333746
  • 2,605
  • 3
  • 18
  • 11

1 Answers1

1

Yes, MySQL can store billions of rows in a single table.
See my answer to Maximum number of records in a MySQL database table.

How to query a table with billions of rows in it is a different problem. You should look into Table Partitioning, with the understanding that how you partition a table must be designed carefully with respect to the queries you need to run against the table.

Also, don't use MyISAM if you don't want your data to become corrupted. Use InnoDB.

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828