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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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. – Phil Apr 21 '13 at 22:45
thank you for your answer – Arash Mousavi Apr 22 '13 at 0:25
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

Your Answer


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.