Search code examples
mysqlquery-optimization

Performance impact of foreign key type in Mysql

We have two types of foreign key:

1 - A foreign key composed of two unsigned int fields (most commom) 2 - A foreign key composed of two varchar fields

The question: We gonna have an negative performance impact by using varchar FK? if yes, to what extent? I would appreciate if anyone has any official sources on this too

Solution

  • All else equal, a good rule of thumb is to prefer INTEGER keys for at least two reasons:

    1. They are generally faster. With VARCHARs there is usually a performance penalty dealing with collations, etc.
    2. There is a lesser chance for mistakes. For example "cat", "CaT" and "cAT" would all be treated equal with the default collation, which is case INsensitive.

    That said, sometimes using VARCHAR primary/foreign keys can be more convenient. That entirely depends on the specific case, though.

    As for the performance impact, keep in mind that in most cases the difference would be negligible. But if you really need to find the fastest solution, there are multiple things to consider:

    • How big are those VARCHAR values? Values like "abc" are one thing, but how about "SomethingVeryLongHere124511234513412352345234";
    • How often would the DB need to retrieve data from disk, as opposed to memory cache;
    • If you use "artificial" INTEGER keys, that might mean an extra level of normalization, which sometimes may help in terms of performance, but it could also make the situation worse, due to extra lookups for the string values (if that's what you would need to read anyway);
    • etc.