views:

226

answers:

2

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?

+5  A: 

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.

Greg
+1  A: 

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.

Everett