0

Sorry for my incorrect terminology (as I don't know what to call that).. does it matter (with regard to performance) if unique (index) key starts from 0 and from i.e. 100000 ?

For example, if I have

ID     |  Brand
----------------
1      | BMW
2      | FERARRI

or

ID     |  Brand
----------------
100000 | BMW
100001 | FERARRI

will be there any difference for performance/queries? I ask that because I have a PHP function that deletes everything and inserts new values. Thus, delete/insert/delete/insert causes to increase the ID more and more, and I am afraid, if I will eventually meet a problem? Or that's not a problem?

1
  • 1
    Side note : instead of remove all rows with DELETE . use TRUNCATE if possible . It's way more efficient. It also may reset autoincrement field .
    – a1ex07
    Oct 6, 2016 at 20:32

2 Answers 2

2

It is possible that smaller values would be slightly more efficient. But if your code is so efficient that one of your larger bottlenecks is the size of your integer keys you are way, way, way ahead of the game and have little left to optimize.

It is possible that the database you are using will use a different amount of space to store different numeric values where smaller numbers tend to use less space. Not every database will do this but some will (for example, Oracle uses a variable number of bytes to represent different numbers). If this is the case for your application, then rows with smaller keys will generally be smaller on disk which means you can read more rows per I/O request, fit more keys in each index block, and cache more rows per kb of RAM. Potentially, you might find that restarting the key would allow you to use a smaller data type for the key but that is pretty unlikely. All these will have some potential performance benefit.

That said, the size of the benefit is likely to be minimal. In most tables, the size of the primary key will be a small contribution to the size of the row so saving a byte or two on a key will probably lead to slightly more free space on each page/ block which would negate the performance benefit. And even if you get slightly more data on each page/ block, it's pretty unlikely that you'd be able to measure the improvement. Yes, you might be slightly more likely to find a row in cache but it would be tough to quantify such a small improvement.

3

Your database shouldn't care, and to it a number is a number is a number in this context. Performance will be the same either way here.

Just make sure that you select an appropriate data type so that you have enough room to "grow" and don't hit the upper bounds of that data type in your expected usage (e.g. don't use a smallint if you're going to need your IDs to go out to 2 billion).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.