Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question

2 Answers

up vote 1 down vote

"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

share|improve this answer
up vote 0 down vote

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.

share|improve this answer
3  
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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