Character vs Integer primary keys

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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.