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 designing a database with multiple lookup tables containing possible attributes of the main entities. I'm thinking of using a 4 or 5-character key to identify these lookup values rather than an auto-incrementing integer so that when I store these attribute IDs on the main tables I'll see meaningful values rather than just random numbers.

What are the performance implications of using a character field as a primary key rather than an integer?

I'm using MySQL if that matters.

[Edit]
These lookup tables have new records added infrequently. They are manually maintained, and the character-based keys are manually created as well. Here's an example:

      CUISINES
 ID      Description
-----  --------------
CHNSE  Chinese
ITALN  Italian
MXICN  Mexican
share|improve this question
3  
Since you are looking for comments regarding performance of this design choice, I'm adding this as a comment, rather than an answer: Since you are using a non-arbitrary value that holds meaning for your key, you should consider what happens when that value needs to change. Arbitrary keys are nice because they never have to change when other attributes of the record change. Just food for thought. –  ScottCher Jan 5 '11 at 14:50
    
If you're just tracking by country of origin, and not specific regional cuisines (Cantonese, Sichuan, Sicilian, Umbrian, Calabrian, Yucatecan, Oaxacan, etc.), you could always just use ISO 3166 codes. –  Joe Jan 5 '11 at 20:25
    
Thanks. Your question forced me to look at this question more thoroughly than I had in the past (I used to do this your way), and saved me a lot of scalability and speed grief by doing it the standard way. –  hobs Mar 20 '13 at 21:54
add comment

3 Answers

up vote 16 down vote accepted

It Depends on your engine. Common wisdom is that reads are cheap, a few bytes here and there will not significantly impact the performance of a small to medium size database.

More importantly, it depends on the uses to which you will put the primary key. Integer serials have the advantage of being simple to use and implement. They also, depending on the specific implementation of the serialization method, have the advantage of being quickly derivable, as most databases just store the serial number in a fixed location, rather than deriving it with Select max(ID)+1 from foo on the fly.

The question becomes: how does a 5 character key present a "meaningful value" to you and to the application? How is this value created, and does it take more or less time than finding an incrementing serial number. While there is a trivial amount of space saved in some integers, the vast majority of systems will ignore this space savings.

Additional answer, based on the edit: There are no performance implications, save that the character scheme requires that there never be an automatic engine, as your "keys" are underivable. For your specific domain, don't bother with artificial keys, and just use Chinese, Japanese and Thai as key names. While you cannot guarantee uniqueness over any possible application, in your scope it is much more reasonable to use them instead of horrible and forced 5 character abbreviations. There are no significant performance impacts until you get to the millions of tuples.

share|improve this answer
    
Thanks for the response. See my clarifications above. –  BenV Jan 5 '11 at 2:20
2  
@BenV Edited. Just go ahead and use "Chinese" and "Japanese" for the keys. a 2 byte saving for is not worth the readability cost. –  Brian Ballsun-Stanton Jan 5 '11 at 2:24
2  
But wouldn't having the primary key be a string slow down the lookup since comparing strings takes longer than comparing integers? Or are most DBMSes written to mitigate this issue? –  indyK1ng Jan 5 '11 at 20:17
    
@Brian: If I have 10,000 recipes doesn't the different between a 5-character and 20-character key start to add up? –  BenV Jan 8 '11 at 20:41
1  
Nope! :) Space is cheap When you're talking 10,000,000 recipies that you're doing OLAP operations on, then, maybe. With 10k recipies, you're looking at 150k of space. –  Brian Ballsun-Stanton Jan 9 '11 at 1:09
show 3 more comments

I think, there is not problem with performance for rarely changed table. Maybe you will have problems with design in the future. I suggest you not to use business data as primary key because of business changes. Use any additional primary key to "link" tables in your model. Any business changes will NOT impact on related to this one tables.

share|improve this answer
add comment

The real question is whether DB query performance is at all significant for you application and DB size. If your query takes microseconds, saving a few of those microseconds by using Int keys isn't worth the readability/maintainability penalty. However, if your query takes minutes, then saving some of those minutes may be worth the pain of Int keys.

Below is why I think integers can save you query time (as a percentage of your overall query time), but the SkySpark founders can explain it better than me. Full disclosure, my employer pays SkySpark a lot of money to use their DB and I'm trying to build something better/faster.

If you have a lot of sequential data (log files, time series, analytics, text or speech corpora) that have links (relationships) to any of your lookup tables, you'll find that storage space is critical for query speed, despite @Ballsun-Stanton's correct analysis of how cheap space is in $. Because most query time (for sequential data) is spent reading the disk, space isn't cheap in terms of time (as a percent of overall query time). So, unless your RDB automatically and efficiently compresses/decompresses all foreign keys (keys to related records), you'll want all your keys to be Int, which are the most efficient in terms of disk space (and read speed) per unit of information content (entropy). FYI MyISAM in MySql places restrictions on what you can do with compressed data rows (read only). In other words Automatically incremented integers are already compressed as much as is theoretically possible, given the low minimum size limitation on most DB integer fields. And that compression comes without:

  1. query-time compression/decompression penalty
  2. query-time disk read penalty
  3. read-only or other DB restrictions on compressed data records or keys

There's a reason why popular, efficient ORMs like Django default to auto-incrementing integers for PKs and why other SO questions have come to the same conclusion.

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.