Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm considering using mediumint or bigint for an ID column. I prefer to use bigint, because that way I'm very sure that the huge numbers that the client sometimes uses are stored just fine. However, I can also technically split up the numbers, and that way use a mediumint. I do not care about storage, but I do care about speed/performance. Is it better to use mediumint than bigint?

This is on an indexed, auto-increment ID column which will be used a lot for SELECT queries.

share|improve this question
add comment

3 Answers

Why does the user know the value of the ID column? This should be abstracted from them. They should be able to identify their data by some other natural key (even if this is only implemented as a unique constraint and you use the surrogate ID as a primary key for performance reasons). If you take away the ID column and you can no longer uniquely identify a row, there is likely an issue with the design.

While you can save some space and improve performance in small ways by choosing a different data type, you need to balance performance with actual business requirements. If you're going to exceed the bound of a smallint or mediumint then you shouldn't use either. Don't make your design complex for the exception (e.g. "splitting up the numbers") to try and optimize for the general rule. This complexity is unnecessary IMHO and will only lead to problems.

In SQL Server we can take advantage of data compression, which essentially treats a bigint column in the following way:

  • if the number fits in a tinyint (0-255), it takes 1 byte
  • if the number fits in a smallint (-32K -> 32K), it takes 2 bytes
  • if the number fits in an int (-2Bn -> 2Bn), it takes 4 bytes
  • only if the number exceeds the bounds of an int does it take 8 bytes

(This is a simplification. And this is the worst case where it treats each value independently only if values on a page are unique and non-sequential, which wouldn't be the case for an auto-increment column. In addition page compression uses other methods like dictionary/prefix, which can reduce storage even further.)

Of course compression trades CPU for I/O, so while you save raw storage and reduce I/O, you pay in CPU. Even today most systems are I/O-bound, so this is a benefit, but shouldn't be considered if you're CPU-bound.

I don't believe there is similar functionality in MySQL, but I am not certain. If there is you should look into it.

And one final note. You say "I do not care about storage, but I do care about speed/performance." If you care about speed/performance, then you should care about storage. The more pages it takes to store your data, the harder the engine is going to have to work to read/write those pages, perform seeks, etc. But again, there needs to be balance. You shouldn't take an extra four weeks over-engineering a solution that is going to save you 10 seconds a week.

share|improve this answer
1  
Kind of OT, but you mention Why does the user know the value of the ID column? . Isnt this acceptable behaviour in some cases? Example: students in a school will know their roll numbers, and that will probably be the only value uniquely identifying them. The same can be said for a bank account number as well. –  Akash Nov 28 '12 at 8:04
    
@Akash note that I said should and not must always. In most cases there is no benefit to the user knowing surrogate key values - in fact that can be problematic if you later change your surrogate model. Is this one of those cases? I have no idea. Hence the why? question and not "you're doing it wrong." –  Aaron Bertrand Nov 28 '12 at 12:59
add comment

As far as I understand your question, you are asking about type for primary key column. So why do you care about user input? If it's a primary key, it should be surrogate, and user has no control on what is stored there. The only thing user cares is uniqueness of that column.

Performance itself depends on size of other columns, fragmentation, typical DML operations, presence/absence of constraints, indexes, etc. In addition, I believe 'pure' performance of different types is diffent for different OS/CPU architecture

I would completely ignore potential performance difference between various integer type for primary key and use the type that makes more sense. .

share|improve this answer
add comment

If your tables are small, it does not matter. In particular, if all the data and indexes are projected to fit in innodb_buffer_pool_size, then it won't make much difference. But if the tables are too big to be cached, then...

"Count the disk hits". or "Smaller --> more cacheable --> faster".

MEDIUMINT is always 3 bytes; BIGINT is always 8.

Also, use UNSIGNED and/or NOT NULL when appropriate.

Later versions of InnoDB (and XtraDB) have some compression; I don't know if it applies to INTs. InfoBright does a significant job of compressing. TokuDB also compresses.

In InnoDB, the PRIMARY KEY is implicitly part of every secondary key. So, that multiplies the storage cost (hence I/O time) for a BIGINT PK. This is also why a VARCHAR is usually not a good idea for an InnoDB PK.

If the client's keys are really huge integers (BIGINTs, UUIDs, GUIDs, MD5s), it might be better to "normalize" them. That is, build a mapping from their over-sized keys into a MEDIUMINT UNSIGNED (or other smallish INT). This adds a lot of coding on your part, but may be worth the effort.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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