Comments on: Migrating to utf8mb4: Things to Consider

By: Vito Meuli

Vito Meuli — Tue, 20 Feb 2024 11:16:13 +0000

The query to check index length problems fails for me on my MySql 8.0.36, I can’t understand why. It says “syntax error”.
I rewrote it as following (I hope I did it well):

WITH indexes AS (
    SELECT 
        SUBSTRING_INDEX(t.name, '/', 1) AS database_name, 
        SUBSTRING_INDEX(t.name, '/', -1) AS table_name, 
        i.name AS index_name, 
        t.row_format AS row_format
    FROM 
        information_schema.innodb_indexes i 
    JOIN 
        information_schema.innodb_tables t ON i.table_id = t.table_id
),
tables AS (
    SELECT 
        indexes.database_name, 
        indexes.table_name, 
        indexes.index_name, 
        indexes.row_format, 
        GROUP_CONCAT(kcu.column_name) AS columns,
        SUM(c.character_maximum_length) * 4 AS index_len_bytes
    FROM 
        indexes 
    JOIN 
        information_schema.key_column_usage kcu ON indexes.database_name = kcu.table_schema 
            AND indexes.table_name = kcu.table_name 
            AND indexes.index_name = kcu.constraint_name
    JOIN 
        information_schema.columns c ON kcu.column_name = c.column_name 
            AND indexes.database_name = c.table_schema 
            AND indexes.table_name = c.table_name
    WHERE 
        c.character_maximum_length IS NOT NULL
    GROUP BY 
        indexes.database_name, 
        indexes.table_name, 
        indexes.index_name, 
        indexes.row_format
)
SELECT 
    * 
FROM 
    tables 
WHERE 
    index_len_bytes >= 768;

WITH indexes AS (

SELECT

SUBSTRING_INDEX(t.name, '/', 1) AS database_name,

SUBSTRING_INDEX(t.name, '/', -1) AS table_name,

i.name AS index_name,

t.row_format AS row_format

FROM

information_schema.innodb_indexes i

JOIN

information_schema.innodb_tables t ON i.table_id = t.table_id

tables AS (

SELECT

indexes.database_name,

indexes.table_name,

indexes.index_name,

indexes.row_format,

GROUP_CONCAT(kcu.column_name) AS columns,

SUM(c.character_maximum_length) * 4 AS index_len_bytes

FROM

indexes

JOIN

information_schema.key_column_usage kcu ON indexes.database_name = kcu.table_schema

AND indexes.table_name = kcu.table_name

AND indexes.index_name = kcu.constraint_name

JOIN

information_schema.columns c ON kcu.column_name = c.column_name

AND indexes.database_name = c.table_schema

AND indexes.table_name = c.table_name

WHERE

c.character_maximum_length IS NOT NULL

GROUP BY

indexes.database_name,

indexes.table_name,

indexes.index_name,

indexes.row_format

)

SELECT

FROM

tables

WHERE

index_len_bytes >= 768;

By: Sveta Smirnova

Sveta Smirnova — Wed, 08 Nov 2023 16:11:03 +0000

In reply to Natarajan Chidhambharam. It depends on your circumstances. For example, if you use REDUNDANT or COMPACT row formats and the formula shows that after conversion, index length would exceed 767 bytes but would not exceed 3072 bytes, you can convert your tables into DYNAMIC or COMPRESSED row formats. If your tables already use DYNAMIC or COMPRESSED row formats and the index length will increase 3072 bytes, you can only modify the index definition, for example, by using column prefixes instead of full column length.

By: Natarajan Chidhambharam

Natarajan Chidhambharam — Tue, 07 Nov 2023 21:41:03 +0000

Under the Index storage requirement - "Once you have identified such indexes, check the columns and adjust the table definition accordingly." Can you please advise what we should do if we have identify such indexes from the query?

By: Sveta Smirnova

Sveta Smirnova — Wed, 05 Oct 2022 12:18:28 +0000

In reply to Mark Gruenberg. No. INNODB_SYS_INDEXES and INNODB_SYS_TABLES were renamed to INNODB_INDEXES and INNODB_TABLES in MySQL 8.0

By: Mark Gruenberg

Mark Gruenberg — Tue, 04 Oct 2022 20:05:48 +0000

Is this an error in the query :

FROM information_schema.INNODB_INDEXES i JOIN information_schema.INNODB_TABLES t USING(TABLE_ID)

should it be
FROM information_schema.INNODB_SYS_INDEXES i JOIN information_schema.INNODB_SYS_TABLES t USING(TABLE_ID)

By: Sveta Smirnova

Sveta Smirnova — Fri, 01 Apr 2022 11:56:16 +0000

In reply to dimus. It depends if their utf8mb4 implementation was ported from MySQL or not.If ported: performance should be same. If not: I haven't seen benchmarks that compare utf8mb4 and utf8mb3 performance in MariaDB.

By: dimus

dimus — Thu, 31 Mar 2022 12:13:06 +0000

>It is faster than utf8mb3
And what about performance utf8mb3 vs utf8mb4 in MariaDB?

By: Bernt Marius Johnsen

Bernt Marius Johnsen — Tue, 29 Mar 2022 14:33:09 +0000

Another issue worth mentioning: Migrating from utf8(mb3) to utf8mb4 may break unique constraints since the collations are not identical. See https://dev.mysql.com/blog-archive/mysql-8-0-collations-migrating-from-older-collations/ and https://dev.mysql.com/blog-archive/mysql-8-0-collations-migrating-from-older-collations-part-2/