mysql_collate_benchmark.md

Test Parameters

MysQL v8.0.11 database created with utf8mb4 character set and the appropriate utfmb4_* collate. Filled with 500k randomized rows. Each benchmark to be run 5 times, with the average calculated over the 5 runs.

In Setup and Benchmarks you will see utf8mb4_0900_ai_ci. Replace this with the collate you are wanting to test with.

Setup

CREATE DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

USE test;

CREATE TABLE test(
  ID INT(11) DEFAULT NULL,
  Description VARCHAR(20) DEFAULT NULL
) ENGINE = INNODB CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

DELIMITER //

CREATE PROCEDURE randomizer()
BEGIN
  DECLARE i INT DEFAULT 0;
  DECLARE random CHAR(20) ;
  theloop: loop
    SET random = CONV(FLOOR(RAND() * 99999999999999), 20, 36);
    INSERT INTO test VALUES (i+1, random);
    SET i=i+1;
    IF i = 500000 THEN
      LEAVE theloop;
    END IF;
  END LOOP theloop;
END; //

DELIMITER ;

Benchmarks

Simple Select

DELIMITER //

CREATE PROCEDURE benchmark_simple_select()
BEGIN
  DECLARE i INT DEFAULT 0;
  theloop: loop
    SELECT *
    FROM test
    WHERE Description = 'test' COLLATE utf8mb4_0900_ai_ci;
    SET i = i + 1;
    IF i = 30 THEN
      LEAVE theloop;
    END IF;
  END LOOP theloop;
END; //

DELIMITER ;

Select Like

DELIMITER //

CREATE PROCEDURE benchmark_select_like()
BEGIN
  DECLARE i INT DEFAULT 0;
  theloop: loop
    SELECT *
    FROM test
    WHERE Description LIKE '%test' COLLATE utf8mb4_0900_ai_ci;
    SET i = i + 1;
    IF i = 30 THEN
      LEAVE theloop;
    END IF;
  END LOOP theloop;
END; //

DELIMITER ;

Order By

DELIMITER //

CREATE PROCEDURE benchmark_order_by()
BEGIN
  DECLARE i INT DEFAULT 0;
  theloop: loop
    SELECT *
    FROM test
    WHERE ID > FLOOR(1 + RAND() * (400000 - 1))
    ORDER BY Description COLLATE utf8mb4_0900_ai_ci LIMIT 1000;
    SET i = i + 1;
    IF i = 10 THEN
      LEAVE theloop;
    END IF;
  END LOOP theloop;
END; //

Results

Benchmark	Collate	Average
Simple Select	utf8mb4_general_ci	4.716s
Simple Select	utf8mb4_unicode_ci	4.87s
Simple Select	utf8mb4_0900_ai_ci	4.942s
Select Like	utf8mb4_general_ci	6.75s
Select Like	utf8mb4_unicode_ci	6.568s
Select Like	utf8mb4_0900_ai_ci	6.496s
Order By	utf8mb4_general_ci	2.314s
Order By	utf8mb4_unicode_ci	5.222s
Order By	utf8mb4_0900_ai_ci	2.818s