0

I am planning a database which has a mini chart of accounts. My account codes are names like:

  • CURRENT_DEBTORS
  • CURRENT_CREDITORS
  • CASH_AT_BANK

I am thinking of using the account codes as a primary key in the account database, which would mean it would be a foreign key in a transactions table. The field would be something like CHAR(20) in MySql. The transactions table would get about 10,000 entries per month.

How will this long-ish character index affect performance? I can use a surrogate key if I have too, but would prefer not too. (I'm not too worried about disk space, of course).

DatsunBing
  • 8,684
  • 17
  • 87
  • 172
  • Is that `CHAR(20) utf8`? If so, that takes 60 bytes. Make it `CHARACTER SET ascii`, if appropriate. – Rick James Dec 09 '15 at 01:33
  • There are times when a 'natural' PK is actually faster and smaller than adding a surrogate `AUTO_INCREMENT` PK. One example is when there is no secondary key. – Rick James Dec 09 '15 at 01:35

1 Answers1

-1

Technically, the answer to your question is that won't have a huge impact on performance, but it will have some, and you would need to test it on your system to know the real impact.

Practically, however, the answer is: don't do it that way. Store those codes in a table, give each a unique integer id (an AUTO_INCREMENT column) and make that integer your foreign key. That's properly normalized, uses much less space than an extra CHAR(20) in every row, and will impose much less of a performance hit.

elixenide
  • 44,308
  • 16
  • 74
  • 100