I was reading MySQL documentation and I was wondering whether MyISAM's "static" table format also applies on InnoDB or not? Do I gain performance, if I use CHAR
instead of VARCHAR
when the real lengths varies between 0 and 100 characters or is using VARCHAR(100)
better?
InnoDB does have fixed-width rows and there can be some advantage to declaring all columns in a table as fixed-width. The main benefit is that all "holes" in a page can be reused for any insertion, since all rows are the same length. This makes space re-use somewhat more efficient. I would not try to force fixed-width for any strings over a few dozen bytes though, as the cost of storing the extra data per page (and thus fewer rows per page) would quickly overwhelm any savings/gains you'd get from more efficient space re-usage.
However, having fixed-width rows does not allow for any optimization of row-scanning (like it does for MyISAM) whatsoever. InnoDB's row storage is always page-based and uses a B+tree with doubly-linked lists of pages, and singly-linked records within a page. Row traversal is always using these structures and can't be done any other way.