Re: Varchar field type as primary key and basis of index

[Vince Gatto]

For MyISAM, which caches index pages in the key buffer, you're looking at the difference between 4 bytes per row (32-bit integer primary key) and let's say an average of 20 bytes for your varchar. That's a 5x increase, so you'll run into memory problems sooner.

For InnoDB, which uses clustering (the rows are stored in primary key order), secondary indexes use the primary key value to refer to a row. So this means the size of any secondary index will also increase. Also, inserting in "random" order, instead of sequentially into a clustered table will be a little slower.

When sorting or grouping, MySQL will try to use in-memory tables which don't allow varchar types. Instead, it will automatically convert it to char(45) which will always use all 45 bytes regardless of the actual value. This means larger in-memory temp tables which means you'll be more likely to need to create a temp table on disk which is when things really start to slow down.

Hope that helps.