Take the 2-minute tour ×
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.

I can't understand this statement in Optimizing Data Size:

Declare columns with identical information in different tables with identical data types, to speed up joins based on the corresponding columns.

Can anyone describe this statement with example?

share|improve this question
3  
Imagine you have "1337" stored as a char/varchar in one column and as a number in another column. It is computationally expensive to convert between the two. Even more so for dates and floating point numbers. –  FreshPhilOfSO Apr 21 '13 at 22:45
    
thank you for your answer –  Arash Mousavi Apr 22 '13 at 0:25
add comment

1 Answer

up vote 2 down vote accepted

If you need to JOIN or compare columns, then ensure they are the same datatype. This avoids implicit conversions which usually invalidate index usage.

However, for JOINs especially this can be completely prevented by defining a foreign key constraint: this forces you to have the same datatype. See MySQL docs which says:

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same

share|improve this answer
add comment

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.