I am try to do some performance testing with varchar foreign keys vs int foreign keys and was wondering how foreign key constraints effect performance of MySQL.

I assume that they effect inserted/updates to a degree however I am not to worried about that in my testing. I was wondering if they effect SELECT statements and if they effect data/index size?


"integers are cheaper to compare than characters, because character sets and collations (sorting rules) make character comparisons complicated. Integers are very good when using as an index column." - HottestGuide


SELECT statements are not affected by FKC's as they are only enforced when data is changed (UPDATE, DELETE, INSERT, etc.)

In MySQL referenced FK columns are required to be indexed (for performance) so you might have extra indices. But you would usually use the FK's heavily in JOIN's either way, so this is the normal SELECT/INSERT-DELETE-UDPATE speed tradeoff.

However, in most applications the slight performance hit is largely outweighed by the improved data integrity assurance.

  • 4
    Actually in some DBMS, SELECTs can be influenced by FKs as the optimizer might remove redundant joins based on FK information - this is usually used to improve the performance of the statement. MySQL is not smart enough to do an optimization like that as far as I know. – a_horse_with_no_name Apr 1 '12 at 10:11
  • okay, but they should never be negatively affected. – dtech Apr 1 '12 at 10:17
  • yes, absolutely – a_horse_with_no_name Apr 1 '12 at 10:20
  • @a_horse_with_no_name: It's in MariaDB 5.1: Table elimination – ypercubeᵀᴹ May 1 '12 at 11:00
  • The question about performance is: Does the index size affect Join performance? (besides the known drawback that a wide (clustered) PK in InnoDB means all other non-clustered indexes in that table will be wide, too. As a VARCHAR(255) FK means there's a VARCHAR(255) PK in the other table. ) – ypercubeᵀᴹ May 1 '12 at 11:07

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.