CODEWITHSUNDEEP
mysqldatabase
MartinMoizard
MartinMoizard

Reputation: 6680

How to handle a large amount of data in a specific table of a database

I am working on a project where I constantly insert rows in a table and within a few days this table is going to be very big and I came up with a question and can't find the answer: what is going to happen when I'll have more rows than 'bigint' in that table knowing that

I have an 'id' column (which is an int)? Does my database (MySQL) can handle that properly? How does big companies handle that kind of problems and joins on big tables?

I don't know if there are short answers to that kind of problems but any lead to solve my question would be welcome!

Upvotes: 3

Views: 2023

Answers (6)

vls
vls

Reputation: 2319

You would run out of storage before you run out of BIGINT primary key sequence.

Unsigned BIGINT can represent a range of 0 to 18,446,744,073,709,551,615. Even if you had a table with a single column that held the primary key of BIGINT type (8 bytes), you would consume (18,446,744,073,709,551,615×8)÷1,024^4 = 134,217,728 terabytes of storage.

Also maximum size of tables in MySQL is 256 terabytes for MyISAM and 64 terabytes for InnoDB, so really you're limited to 256×1,024^4÷8 = 35 trillion rows.

Oracle supports NUMBER(38) (takes 20 bytes) as largest possible PK, 0 to 1e38. However having a 20 byte primary key is useless because maximum table size in Oracle is 4*32 = 128 terabytes (at 32K block size).

Upvotes: 5

David Ly
David Ly

Reputation: 31586

To put BIGINT even more into perspective, if you were inserting rows non-stop at 1 row per millisecond (1000 rows per second), you would have 31,536,000,000 row per year.

With BIGINT at 18,446,744,073,709,551,615 you would be good for about 18 million years.

Upvotes: 1

Parris Varney
Parris Varney

Reputation: 11478

You could make your bigint unsigned, giving you 18,446,744,073,709,551,615 available IDs

Upvotes: 0

ajreal
ajreal

Reputation: 47321

numeric data type

If this column is primary key, you are not able to insert more rows.

If not a primary key, the column is truncated to the maximum value it can presented in that data type.

You should change id column to bigint as well if you require to perform join.

You can use uuid to replace integer primary key (for big companies),

take note that uuiq is string, and your field will not longer in numeric

Upvotes: 1

RickyB
RickyB

Reputation: 1

Big companies handle it by using DB2 or Oracle

Upvotes: -1

Rafid
Rafid

Reputation: 20189

That is one of the big problems of every website with LOTS of users. Think about Facebook, how many requests do they get every second? How many servers do they have to store all the data? If they have many servers, how do they separate the data across the servers? If they separate the data across the servers, how would they be able to call normal SQL queries on multiple servers and then join the results? And so on. Now to avoid complicating things for you by answering all these questions (which will most probably make you give up :-)), I would suggest using Google AppEngine. It is a bit difficult at the beginning, but once you get used to it you will appreciate the time you spent learning it.

If you are only having a database and you don't have many requests, and your concern is just the storage, then you should consider moving to MSSQL or -better as far as I know- Oracle.

Hope that helps.

Upvotes: 1

Related Questions