Is there a REAL performance difference between INT and VARCHAR primary keys?

by

You make a good point that you can avoid some number of joined queries by using what’s called a natural key instead of a surrogate key. Only you can assess if the benefit of this is significant in your application.

That is, you can measure the queries in your application that are the most important to be speedy, because they work with large volumes of data or they are executed very frequently. If these queries benefit from eliminating a join, and do not suffer by using a varchar primary key, then do it.

Don’t use either strategy for all tables in your database. It’s likely that in some cases, a natural key is better, but in other cases a surrogate key is better.

Other folks make a good point that it’s rare in practice for a natural key to never change or have duplicates, so surrogate keys are usually worthwhile.

More Related Contents:

  1. MyISAM versus InnoDB [closed]
  2. How to properly create composite primary keys – MYSQL
  3. What’s the difference between MyISAM and InnoDB? [duplicate]
  4. Why is MySQL InnoDB insert so slow?
  5. MySql: MyISAM vs. Inno DB! [closed]
  6. UUID performance in MySQL?
  7. How can I check MySQL engine type for a specific table?
  8. MySQL InnoDB: autoincrement non-primary key
  9. Why doesn’t MySQL’s MyISAM engine support Foreign keys?
  10. Should I always prefer MySQL InnoDB over MyISAM?
  11. Composite Primary Key performance drawback in MySQL
  12. MYSQL OR vs IN performance
  13. How do I quickly rename a MySQL database (change schema name)?
  14. How to Perform an UPSERT so that I can use both new and old values in update part
  15. JDBC batch insert performance
  16. TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes
  17. MySQL InnoDB not releasing disk space after deleting data rows from table
  18. Prevent auto increment on MySQL duplicate insert
  19. auto increment primary leaving gaps in counting
  20. Bogus foreign key constraint fail
  21. How to optimize COUNT(*) performance on InnoDB by using index
  22. MySQL: Inner join vs Where [duplicate]
  23. How to make a primary key start from 1000?
  24. Database corruption with MariaDB : Table doesn’t exist in engine
  25. Defining Composite Key with Auto Increment in MySQL
  26. MySQL DROP all tables, ignoring foreign keys
  27. MySQL – Meaning of “PRIMARY KEY”, “UNIQUE KEY” and “KEY” when used together while creating a table
  28. Why is connecting to MySQL server so slow?
  29. Mysql performance on 6 million row table
  30. How can I rebuild indexes and update stats in MySQL innoDB?

Leave a Comment