INT vs. VARCHAR Primary Keys in MySQL: Does Data Type Really Impact Performance?

Performance Impact of INT vs. VARCHAR Primary Keys in MySQL

The debate surrounding the optimal choice between INT and VARCHAR as primary keys for reference lists in MySQL has been a topic of discussion among developers. Some argue that INT offers superior performance due to its numerical nature, while others posit that VARCHAR provides direct presentation of referenced information.

To address this issue, we conducted a comprehensive performance test on various table configurations and query types. Our findings indicate that there's negligible difference in performance between INT and VARCHAR primary keys when used for reference lists.

Experimental Setup

• System specifications: Intel® Core™ i7-7500U CPU @ 2.70GHz × 4, 15.6 GiB RAM, 148.6 GB SSD drive
• MySQL version: Ver 14.14 Distrib 5.7.20, for Linux (x86_64)
• Tables: 6 tables created with INT (10), CHAR(6), and VARCHAR(63) data types as primary keys, with and without indexes on another column (myindex)

Test Procedure

• 10 million rows inserted into each table
• Execution of count(*) query on each table using myindex as filter
• Measurement of execution time using BENCHMARK() function

Results

Table Name	Time (sec)
jan_int	21.30
jan_int_index	18.79
jan_char	21.70
jan_char_index	18.85
jan_varchar	21.76
jan_varchar_index	18.86

As illustrated in the table above, query execution times were virtually identical across all six table configurations, both with and without indexes.

Conclusion

Our experimental findings suggest that, for the specific use case of reference lists, there's no significant performance difference between using INT and VARCHAR as primary keys in MySQL. Consequently, the choice between the two data types can be guided by other factors, such as clarity and maintainability of the data model.

The above is the detailed content of INT vs. VARCHAR Primary Keys in MySQL: Does Data Type Really Impact Performance?. For more information, please follow other related articles on the PHP Chinese website!