I have a table that has a varchar primary key that's associated with "Base Product Codes". They rarely change, and if they do, it's usually just being deleted or added. I figured it would be better to index part of the product code so I could perform searches on the data based on a partial product code.

As an example, "Base Product Codes" are in the following format:


The table stores the relationship between a product and accessory, and there can be multiple relationships for each product or accessory:

Id          |     Prod     |    Acc    | Val
ABC-AB12_1  |    ABC-AB12  |   ABC1    |  1
ABC-AB12_2  |    ABC-AB12  |   DEF1    |  2
ABC-AB12_3  |    ABC-AB12  |   GHI1    |  A

I'm storing the id for each relationship as the product base code with an integer tacked onto the end of it.

I figured if I needed to to a search for all of the relationships for a particular product that it would be faster to use the primary key and search than it would be to search on the Prod column.

Is that an incorrect assumption? Should I change the Id to be an auto incrementing integer instead?

2 Answers 2


You should have an autoincrement PRIMARY KEY. Since I do not know the storage engine of the table, I will try answer based on both.


In general, a smaller primary key is always better than a bigger one. The PRIMARY KEY for an InnoDB table is stored in the Clustered Index (known within InnoDB as the gen_clust_index). Since an InnoDB Page is 16K, smaller keys will make more keys fit inside an index page.

What should be noted is the fact that for each entry in a Secondary Index, there is Primary Key. Thus, not only will a smaller PRIMARY KEY benefit the table, but all non-unique Indexes will corresponding shrink as well.


Similar principles apply to MyISAM in terms of key sizes and indexes. Additionally, there is an added bonus in your particular case that is not often discussed when it comes to MyISAM.

MyISAM allows you to have an auto_increment key per column value. What do I mean?

Look at the table in your question with additional rows:

Id          |     Prod     |    Acc    | Val
ABC-AB12_1  |    ABC-AB12  |   ABC1    |  1
ABC-AB12_2  |    ABC-AB12  |   DEF1    |  2
ABC-AB12_3  |    ABC-AB12  |   GHI1    |  A
DEF-AB12_1  |    DEF-AB12  |   ABC1    |  1
DEF-AB12_2  |    DEF-AB12  |   DEF1    |  2
DEF-AB12_3  |    DEF-AB12  |   GHI1    |  A
GHI-AB12_1  |    GHI-AB12  |   ABC1    |  1
GHI-AB12_2  |    GHI-AB12  |   DEF1    |  2
GHI-AB12_3  |    GHI-AB12  |   GHI1    |  A

You could replace the Id with an auoincrement value and end up with this:

Id |     Prod     |    Acc    | Val
1  |    ABC-AB12  |   ABC1    |  1
2  |    ABC-AB12  |   DEF1    |  2
3  |    ABC-AB12  |   GHI1    |  A
4  |    DEF-AB12  |   ABC1    |  1
5  |    DEF-AB12  |   DEF1    |  2
6  |    DEF-AB12  |   GHI1    |  A
7  |    GHI-AB12  |   ABC1    |  1
8  |    GHI-AB12  |   DEF1    |  2
9  |    GHI-AB12  |   GHI1    |  A

This you would do if the Id looks likr this:


OK, great. Now here is the added bonus: If you make the PRIMARY KEY look like this:


the data can be stored like this:

Id |     Prod     |    Acc    | Val
1  |    ABC-AB12  |   ABC1    |  1
2  |    ABC-AB12  |   DEF1    |  2
3  |    ABC-AB12  |   GHI1    |  A
1  |    DEF-AB12  |   ABC1    |  1
2  |    DEF-AB12  |   DEF1    |  2
3  |    DEF-AB12  |   GHI1    |  A
1  |    GHI-AB12  |   ABC1    |  1
2  |    GHI-AB12  |   DEF1    |  2
3  |    GHI-AB12  |   GHI1    |  A

How is that possible? Only the MyISAM Storage Engine Has This Mechanism Built In !!!

I have discussed this before:

One more thing: Why have PRIMARY KEY (Prod,Id) as a PRIMARY KEY? This would allow you to sequence each Product ID. Thus, you can look for sequence 3 of one product and sequence 3 of another product.


Whichever way you decide to go, using a smaller autoincrement PRIMARY KEY (4 bytes) make more sense for performance and diskspace than a larger PRIMARY KEY (more than 4 bytes).

Give it a Try !!!

  • This is a fantastic answer! Thank you very much! The table uses the InnoDB engine. Will it make a difference if I add a UNIQUE constraint to the column that contains the actual product id, since that needs to be unique? Commented Jan 22, 2014 at 21:40
  • Adding a UNIQUE constraint should be fine. It will bloat the table and the gen_clust_index, but secondary keys (non-unique indexes) will not bloat. Commented Jan 22, 2014 at 21:44
  • Hi, @RolandoMySQLDBA, if a table has a few tuples (for example less that 20 like the trip type table) and the natural key is varchar with atmost 32 characters, can i use the natural key as a primary key so that I can avoid Joining? (for the InnoDB)
    – Arash
    Commented Sep 29, 2021 at 5:54
  • 1
    @Arash you could but the point of my post is to avoid large keys. They degrade INSERT performance and badly introduces fragmentation. EXAMPLE : Amazon recommends not using VARCHAR for keys for Aurora aws.amazon.com/blogs/database/… Commented Sep 29, 2021 at 12:40
  • Thanks, @RolandoMySQLDBA.
    – Arash
    Commented Sep 29, 2021 at 14:50

Zaemz ! It´s generally a good idea to use integer keys rather than varchar if you don´t have any speciall thought why you have do do it.

But the big question is rather how many rows are we talking about in the table and how frequent it´s used. If you only have a couple of thousand rows and you use a small varchar, then varchar would work almost as god as an integer.

  • The table has roughly 50 thousand rows, and the id's are never longer than 12 characters. Commented Jan 22, 2014 at 21:32
  • Then you definitely should consider to use a integer to get the best performance.
    – carleson
    Commented Jan 22, 2014 at 21:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.