0

I have inherited a (quite) big database (for my standards at least, since by no means I call myself a DBA or DB dev).
So, on this DB they used a unique natural string as the primary key on EVERY table. In my novice experience it seems that is slowing joins down.
Just to get some more info on this, our main table is having roughly 1.5 million rows, the key is a taxing office id number, which might have some leading 0s, so they went and declared this as a varchar(12), in order not to loose the leading 0.
One of our secondary table has more than 3 million rows, and an other one with roughly the same number of rows as the main table. Each of these tables (and many more of smaller size) are using the same varchar(12) as a foreign key to the main table mentioned before.

Questions

  1. Would it make any difference using an int instead of this varchar as key?
  2. If 1=Y then : There are a few applications that are using this database and they are all using this key for joining tables. Is there a way to keep the old columns for joining the tables, but get some advantage using some int column for the key? i.e. can i change the key, but keep the same old queries?

Edit:
One major limitation on changing the queries is that most are not stored procedures. They are hard coded inside the actual applications

Improve this question
  • Ints are faster indeed. When will you refactor your database wrap all the queries into the stored routines like the API. Then you can modify database until stored routines are modified accordingly. – Kondybas Jan 26 '18 at 12:04
  • @Kondybas Iam sorry, dont really understand what you mean to wrap the queries like the API. An other thing (that i forgot to mention) is that most applications dont use stored procedures, they mostly have their queries hardcoded into java & php – Skaros Ilias Jan 26 '18 at 12:53
  • Are the leading 0's significant? As in '000123' is different than '000000123'? If not, you could probably (test, test, test!) get away with just converting them. Searches would still be relatively slow, but the join performance should go way up. No need to change the application. And are they really numbers, no letters at all? – Jonathan Fite Jan 26 '18 at 13:09
  • @SkarosIlias Hardcoding SQL into the apps make DB developing a hell. Avoid that where possible. – Kondybas Jan 26 '18 at 16:42
  • @Kondybas I know queries is should be in stored procedures, but now i stuck with the code already at hand. – Skaros Ilias Jan 29 '18 at 9:16
1

INT is 4 bytes. VARCHAR(12) is a little longer. I would gladly use either for a PRIMARY KEY without worrying about performance.

A BTree is efficiently structured such that a longer key does not impact performance much.

If you switch that value to INT, perhaps with ZEROFILL, the speed up will be small.

If you add an INT AUTO_INCREMENT, then you are likely to slow down some uses of the table. For more details, let's see some queries, including inserts and updates.

Improve this answer
  • i thought varchar(X) uses more bytes than int(X). based on your rep, I am guessing you know better than me... – Skaros Ilias Jan 29 '18 at 9:22
  • VARCHAR(...) with a 1-character string will be shorter than INT. But, in general, VARCHAR(12) will be longer than INT. However, it will not be enough longer to make a big difference. – Rick James Jan 29 '18 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.