MySQL primary key column type for large tables

Question

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?

Answer 1

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.

Answer 2

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.