Which one is faster to get the row? The primary key that carries numbers or that carries characters?

Issue

ID (Int 11) (Primary key) (Auto increment) TITLE
1
2
3
4
5
To 10 million rows
ID (Char 32) (Primary key) TITLE
a4a0FCBbE614497581da84454f806FbA
40D553d006EF43f4b8ef3BcE6B08a542
781DB409A5Db478f90B2486caBaAdfF2
fD07F0a9780B4928bBBdbb1723298F92
828Ef8A6eF244926A15a43400084da5D
To 10 million rows

If I want to get a specific row from the first table, How much time will take approximately, Same thing with the second table, How much time will take approximately?

Is the primary key that carries numbers will be found faster than that carries characters?

I do not want to use auto-increment with int like the first table because of this problem

Solution

It is impossible to tell the exact times needed to retrieve a specific record, because it depends on lots of factors.

In general, numeric values take less storage space, thus scanning the index requires less I/O operations, therefore are usually faster.

However in this specific case the second table looks like a hexadecimal representation of a large number. You can probably store it as a binary value to save storage space.

On top of the above, in general numeric values are not affected by various database and column settings, while strings are (like collation), which also can add some processing time while querying.

The real question is what is the purpose of using the binary representation. 10 million values can easily fit in INT what is the need to have a key which can store way more (32 long hexadecimal value)?

As long as you are within the range of the numeric values and there is no other requirement, just to be able to store that many different values, I would go with an integer.

The ‘problem’ you mention in the question is usually not a problem. There is no need to not have gaps in the identifiers in most caes. In fact in lots of systems, gaps are naturally occurring during normal operations. You most probably won’t reassign the records to other IDs when one record is being deleted from the middle of the table.

Unless there is a semantic meaning of the ID (it should not), I would just go with an AUTO_INCREMENT, there is no need to reinvent the wheel.

Answered By – Pred

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published