Restrictions On InnoDB Tables

• There are several restrictions on InnoDB tables.
  • Do not convert MySQL system tables in the mysql database from MyISAM to InnoDB tables!
  • A table cannot contain more than 1000 columns.
  • The internal maximum key length is 3500 bytes, but MySQL itself restricts this to 1024 bytes.
  • One cannot define a row containing VARCHAR columns with a combined size larger than 65535.
  • The combined size of the InnoDB log files must be less than 4GB.
  • The minimum tablespace size is 10MB.
  • InnoDB tables do not support FULLTEXT indexes.
  • InnoDB supports spatial types, but not indexes on them.
  • SHOW TABLE STATUS does not give accurate statistics on InnoDB tables.
    • Except for the physical size reserved by the table.
    • The row count is only a rough estimate used in SQL optimization.
  • DELETE FROM tbl_name does not regenerate the table but instead deletes all rows, one by one.
  • The default database page size in InnoDB is 16KB.
  • InnoDB does not ignore trailing spaces when comparing BINARY or VARBINARY column values.
  • InnoDB does not allow column names that match the name of an internal InnoDB column.
    • Including DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR and DB_MIX_ID.

Are there guidelines to troubleshooting InnoDB problems?

---

---

© 2007 John Michael Pierobon

Notes