innodbutf8mb4

mySQL optimizations - Compact type type, InnoDB and UTF8MB4

I'm using a community forum site software package. In the dashboard the automated software reports some suggestions to make the site more efficient. The recommendations are/were to:

  1. Use dynamic table type not Compact.
  2. Use InnoDB engine on tables not myIASM
  3. Use UTF8MB4 character encoding.

I guess I have recipes in SQL to adjust the database per each of these recommendations, but the question I have is which order of the changes should it be?

Would it make sense to do the InnoDB conversion first, or the fixed-to-dynamic table type, or the character encoding to UTF8MB4.

I will do all three improvements, I just don't know in MySQL which order makes the most sense to be efficient.

Opinions?

Version of MySQL is fixed at 5.7 (and I cannot change the version due to requirements)

Hosted on a Ubuntu system.

Solution

  • Do you already have data in the tables? Lots of data? Please provide SHOW CREATE TABLE for one of the current tables. There may be some subtle issues that I am missing.

    Do them all at once (per table):

    ALTER TABLE t
    ENGINE = InnoDB,
    CONVERT TO utf8mb4,
    ROW_FORMAT = DYNAMIC;

    See also: http://mysql.rjweb.org/doc.php/myisam2innodb

    Do you need a simple SELECT into information_schema.TABLES to generate all the ALTERs for you?

    The conversion will take some extra disk space; plan for 4 times as much disk as you currently have.

    More

    All three of the changes in that ALTER require a complete copy of the table. They can all be done at the "same" time. That is, it would be slower to break it into multiple ALTERs.

    Think of ALTER this way:

    1. Create a new table with all the changes
    2. Copy all the data over (a la INSERT...SELECT...)
    3. Swap tables