Mysql – VARCHAR primary key – MySQL

foreign keyMySQLperformanceprimary-keyvarchar

Currently, I have a categories table with 2 columns – category VARCHAR(50) NOT NULL PRIMARY KEY and parent VARCHAR(50). The parent column is a foreign key (FK) to the category column.

This seems like the most obvious approach. However, alarm bells are ringing in my head because I am using a VARCHAR column for a primary key, which might slow down operations when querying the table.

I could introduce a third column called cat_id INT AUTO_INCREMENT and set that to be the PK, but it would introduce a new column which have no meaning.

Apart from which one would be faster, what other considerations should be taken?

N.B. I predict there will be at most 1000 categories or so so the number of rows are not very high. However, the categories PK column will be a reference column for many foreign keys, in other tables.

Should I use (unique) usernames as PK's also?

Best Answer

VARCHAR column as Primary Key is not a good choice as normally we create Cluster Index on same column.

Cluster Index on VARCHAR columns is a bad choice because of expected high fragmentation rate. Every new inserted key value will try to find its place somewhere between existing keys and normally cause page split and high index fragmentation. As a result poor performance and extra index rebuild/reorganise cost.

Secondly while using varchar key column as Foreign Key will take extra space as compared to Pseudo key auto-incremented column.

BUT

Clustered index on auto-incremented column may create "hot spot". Read this carefully Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?

Though hot spot could be a problem, when to many users are trying to insert values but still in your case i would like to go for auto-incremented column as compare to varchar.

Related Solutions

Mysql int vs varchar as primary key (InnoDB Storage Engine

I follow the following rules for primary keys:

a) Should not have any business meaning - they should be totally independent of the application you are developing, therefore I go for numeric auto generated integers. However if you need additional columns to be unique then create unique indexes to support that

b) Should perform in joins - joining to varchars vs integers is about 2x to 3x slower as the length of the primary key grows, so you want to have your keys as integers. Since all computer systems are binary, I suspect its coz the string is changed to binary then compared with the others which is very slow

c) Use the smallest data type possible - if you expect your table to have very few columns say 52 US states, then use the smallest type possible maybe a CHAR(2) for the 2 digit code, but I would still go for a tinyint (128) for the column vs a big int which can go up to 2billion

Also you will have a challenge with cascading your changes from the primary keys to the other tables if for example the project name changes (which is not uncommon)

Go for sequential auto incrementing integers for your primary keys and gain the inbuilt efficiencies that database systems provide with support for changes in the future

Mysql – Can a complex primary key made up of 4 fields have only one of this fields set to AUTO INCREMENT

You can do this:

CREATE TABLE mytable
(
    `ID` MEDIUMINT NOT NULL AUTO_INCREMENT,
    `C1` MEDIUMINT NOT NULL,
    `C2` MEDIUMINT NOT NULL,
    `Year` MEDIUMINT NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (C1, C2)
            REFERENCES othertable(pkcol1, pkcol2)
);

ALTER TABLE mytable ADD UNIQUE INDEX(`C1`, `C2`, `Year`, `ID`);

Adding the ID column to the unique index will guarantee that you can have the same values for C1, C2 and Year in your table. If you don't want that, remove ID from the ALTER TABLE statement.

The syntax of the FOREIGN KEY is for a multiple column key in another table called othertable. pkcol1 and pkcol2 would be the corresponding columns in that table.

ID would be your fourth column in the question example code you presented.

Best Answer

Related Solutions

Mysql int vs varchar as primary key (InnoDB Storage Engine

Mysql – Can a complex primary key made up of 4 fields have only one of this fields set to AUTO INCREMENT

Related Question