Does having variable-length columns slow selects on InnoDB table?

Question

With MyISAM having variable length columns (varchar, blob) on the table really slowed queries so that I encountered advices on the net to move varchar columns into separate table.

Is that still an issue with InnoDB? I don't mean cases where introducing many varchar rows into the table causes page split. I just mean should you consider, for example, move post_text (single BLOB field in the table) into another table, speaking performance-wise about InnoDB?

Forbore

Answer 1

As far as I know BLOBs (and TEXTs) are actually stored outside of the table, VARCHARs are stored in the table.

VARCHARs are bad for read performance because each record can be of variable length and that makes it more costly to find fields in a record. BLOBs are slow because the value has to be fetched separately and it will very likely require another read from disk or cache.

To my knowledge InnoDB doesn't do anything differently in this respect so I would assume the performance characteristics hold.

I don't think moving BLOB values really helps - other than reducing overall table size which has a positive influence on performance regardless. VARCHARs are a different story. You will definitely benefit here. If all your columns are of defined length (and I guess that means you can't use BLOBs either?) the field lookup will be faster.

If you're just 'reading' the VARHCAR and BLOB fields I'd say this is worth a shot. But if your select query needs to compare a value from a VARCHAR or a BLOB you're pretty sour.

So yes you can definitely gain performance here but make sure you test that you're actually gaining performance and that the increase is worth the aggressive denormalization.

PS.

Another way of 'optimizing' VARCHAR read performance is to simply replace them by CHAR fields (of fixed length). This could benefit read performance, so long as the increase in disk space is acceptable.

Juncture

As I understand one can suggest to keep BLOB columns last in the table, if BLOBs are rarely queried. This will allow to keep performance on the level of fixed-length row table. – Forbore

This question is from a while ago, but I believe the accepted answer is incorrect on a couple of points. See my answer for details. – Constrictor

Answer 2

InnoDB data completely differently than MyISAM.

In MyISAM all indexes--primary or otherwise--- are stored in the MYI file an contain a pointer to the data stored in the MYD file. Variable length rows shouldn't directly affect query speed directly, but the MYD file does tend to get more fragmented with variable length rows because the hole left behind when you delete a row can't necessarily be filed in with the row you insert next. If you update a variable length value to make it longer you might have to move it somewhere else, which means it will tend to get out-of-order with respect to the indexes over time, making range queries slower. (If you're running it on a spinning disk where seek times are important).

InnoDB stores data clustered in pages in a B-tree on the primary key. So long as the data will fit in a page it is stored in the page whether you're using a BLOB or VARCHAR. As long as you aren't trying to insert inordinately long values on a regular basis it shouldn't matter whether your rows are fixed-length or variable-length.

Constrictor