Index use and column type

Question

I was told today that having a table structure similar to this is bad for performance of indexes 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 ....

I'm 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 affected 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?

Answer 1

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.

• Stu: To put it more bluntly: specifying a width for int at a database level is asinine and asking for trouble.