CODEWITHSUNDEEP
mysqlsqlperformanceperformance-testing
MLQ
MLQ

Reputation: 13511

Is it faster to retrieve a row in MySQL if the primary key is a string (varchar) or an integer?

I'm creating a database for a dictionary. Consider the table WORD. My current plan is to make its primary key word_id which will be an integer, then give it another attribute text which is the textual representation of the word.

However, I thought that since it's a dictionary, all definitions, pronunciations, and parts of speech that apply to a single word are going to be found right under its heading and there is absolutely NO way for other words that have the same spelling to have a different page. Therefore, making text the primary key makes sense, and I won't need to have a word_id.

My question now is, which method will perform faster? It looks like giving it an integer primary key is better since it is an easily comparable primitive type, especially helpful when checking it as a foreign key of other tables. I'm not sure, but I think that if the primary key were a string, checking it against other tables' foreign keys involves an underlying per-character comparison which takes longer, plus the added burden of case-insensitive checking.

Also, if speed is not such a huge issue, are there any other factors I should consider? The database I'm about to use is MySQL.

Upvotes: 1

Views: 431

Answers (4)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112772

Another point that stands in favor of int is that strings lead to the following questions:

  1. Is the comparison case sensitive or not?

  2. Is the text trimmed or does it have additional invisible spaces?

  3. Is the encoding right? (This can be a problem if data is imported/exported from/to another system.)

  4. Meaningful keys are subject to edits, while nobody can edit an identity column and nobody is interested in editing a guid or meaningless int.

Upvotes: 1

pseudocode
pseudocode

Reputation: 361

Although by default an index is created for the primary key and as you have indicated if it is integer id then the matching will be faster. But you can always make an index on any column of a table regardless of what you primary key is. So performance depends more on index rather than primary key.

Upvotes: 1

Diego
Diego

Reputation: 1569

You can check this SO question:

Is there a REAL performance difference between INT and VARCHAR primary keys?

I think it covers your question.

Upvotes: 3

Jason S
Jason S

Reputation: 189896

My question now is, which method will perform faster? It looks like giving it an integer primary key is better since it is an easily comparable primitive type, especially helpful when checking it as a foreign key of other tables. I'm not sure, but I think that if the primary key were a string, checking it against other tables's foreign keys involves an underlying per-character comparison which takes longer, plus the added burden of case-insensitive checking.

My guess is that you're right, but the difference is so small compared to other processing tasks (e.g. network + disk I/O) by the database server that it doesn't matter.

Upvotes: 1

Related Questions