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 was told today that having a table structure similar to this is bad for performance of index's because of the mismatch of column types

Table 1 id - int (7) PK Others ....

Table 2 id int (11) Index (has to exist in table 1 logically - though not enforced through FK) Others ....

Im using MySQL with InnoDB.

The question i suppose could also be related to Varchar 200 vs 220 for example.

I know that the index size will be effected by the extra space per item needed (and storage space in general), probably in the 5-10% area, but will the time taken for joins to happen increase ?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

Phrases like bad for performance are essentially meaningless. Measurements are meaningful; opinions are, umm, not measurements.

But int(7) and int(11) are both 32-bit integers. The number in parens refers to display width; it has nothing to do with storage size or index size.

For varchar() columns, you can create an index that's based on the first 'n' characters. That decision has some performance implications, for example when you're searching for 'wibble' in a column indexed on the first three characters. And there are several subtle points with columns of type "text". Read CREATE TABLE carefully. (Search for "length".)

But I'd be a little surprised if you could measure any difference in joining columns of varchar(200)/varchar(200) and in joining columns of varchar(220)/varchar(200). That is, other than the obvious--storing more than 200 characters in the varchar(220) column.

There's an easy way to tell: write some code in your favorite scripting language, generate a few million rows of sample data, load it, and test it. And post your results here, too.

share|improve this answer
    
To put it more bluntly: specifying a width for int at a database level is asinine and asking for trouble. –  Stu Mar 14 '13 at 22:53
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.