5

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?

2 Answers 2

2

"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

0

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.

5
  • 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.
    – user1822
    Commented Apr 1, 2012 at 10:11
  • okay, but they should never be negatively affected.
    – dtech
    Commented Apr 1, 2012 at 10:17
  • yes, absolutely
    – user1822
    Commented Apr 1, 2012 at 10:20
  • @a_horse_with_no_name: It's in MariaDB 5.1: Table elimination Commented May 1, 2012 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. ) Commented May 1, 2012 at 11:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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