INT or VARCHAR Primary Keys for Reference Lists: Does Performance Really Matter?

INT vs. VARCHAR Primary Keys: Mythbusting Performance Concerns

The choice between INT and VARCHAR primary keys in MySQL has long been debated, particularly regarding reference lists. While INT is the traditional choice, VARCHAR is growing in popularity due to its ability to directly present information, potentially reducing the need for JOINs.

This article aims to investigate the performance implications of using INT vs. VARCHAR primary keys in a specific use case: reference lists like US states and country codes.

Benchmark Results

To assess the performance difference, a series of benchmarks were conducted using a setup consisting of:

• Intel® Core™ i7-7500U CPU @ 2.70GHz × 4
• 15.6 GiB RAM
• Ubuntu 16.04 64-bit
• MySQL Ver 14.14 Distrib 5.7.20

Two sets of tables were created: INT tables and VARCHAR tables, each indexed and unindexed. The tables were then populated with 10 million rows, and a benchmark query was executed against each table to measure the time required to retrieve a specific row.

Benchmark Query Execution Times

The execution times of the benchmark query are as follows:

• jan_int: 21.30 sec
• jan_int_index: 18.79 sec
• jan_char: 21.70 sec
• jan_char_index: 18.85 sec
• jan_varchar: 21.76 sec
• jan_varchar_index: 18.86 sec

Analysis

The results indicate that the performance difference between INT and VARCHAR primary keys is minimal for reference lists. The benchmark query execution times are within a few hundred milliseconds of each other, regardless of whether the key is indexed or not.

This suggests that the performance benefits of reducing JOINs using a VARCHAR primary key are offset by the additional overhead of storing variable-length data.

Conclusion

Based on the benchmark results, it can be concluded that, for reference lists like US states and country codes, the choice between INT and VARCHAR primary keys is primarily a matter of preference rather than performance. The performance difference is negligible, and other factors, such as data consistency and future scalability, may influence the decision.

The above is the detailed content of INT or VARCHAR Primary Keys for Reference Lists: Does Performance Really Matter?. For more information, please follow other related articles on the PHP Chinese website!