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

CodePudding user response：

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.