CODEWITHSUNDEEP
mysqldatabasedatabase-designschema
user80805
user80805

Reputation: 6508

MySQL primary key column type for large tables

I'm working on the project with relatively big DB table(700K rows).

Mistake, I've made while designing DB schema. When rows were increasing, I had to increase ID's column type bigint(x).

Now it is bigint(44). I afraid of setting high x value because I thought that it can significantly slows down performance. Maybe I'm wrong..

Please help me in solving the problem.

What column type can I set once and forget about this issue?

What should I learn better in field of DB schema design?

Upvotes: 8

Views: 8445

Answers (2)

Greg
Greg

Reputation: 321588

When you create a column as BIGINT(44) the "44" is the display width - it does not affect the range of values you can store or the speed at which they are retrieved.

For an auto-incrementing ID you want to use an UNSIGNED number, e.g. BIGINT(44) UNSIGNED. This will double the range of values and add an extra constraint, which is usually a good thing.

An unsigned INT will store up to:

4,294,967,295

An unsigned BIGINT will store up to:

18,446,744,073,709,551,615

you're not going to fill that any time soon.

You don't say how fast your maximum ID is growing - if you're not inserting many rows then you should stick with UNSIGNED INT as it takes less space.

Upvotes: 15

Everett
Everett

Reputation: 9568

I think any primary key will be unsigned by default. In any case, using negative numbers for primary keys is frowned upon at best, and breaks stuff.

Upvotes: 1

Related Questions