Optimise MySQL Performance with Indexing Sep 27th 2021 Words: 301

Background

Recently I received report said one of my API has serious performance issue. After quick investigation, I found the culprit is a SQL query that takes over 50 seconds to complete.

The table schema:

1
2
3
4
5
6
7
8
9
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| uuid | binary(16) | NO | PRI | NULL | |
| name | varchar(256) | YES | | NULL | |
| description | varchar(256) | YES | | NULL | |
| creation | int | NO | | NULL | |
| meta | json | YES | | NULL | |
+---------------+--------------+------+-----+---------+-------+

The problematic query performs a simple selection with filter:

1
2
3
4
5
6
SELECT 
BIN_TO_UUID(uuid) AS uuid
FROM product
WHERE creation < 1632734788
ORDER BY creation DESC
LIMIT 0, 100

The above query spend 52 seconds on a table contains 800k entries.

Solution

Without indexing, each time the SQL query is executed, the SQL server has to scan the whole table and apply the filter condition on each row, result in terrible performance.

Add an index on a new table

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE IF NOT EXISTS product
(
uuid BINARY(16) NOT NULL UNIQUE,
name VARCHAR(256) NOT NULL,
description VARCHAR(1024) NOT NULL,
creation INT,
meta JSON,

PRIMARY KEY (uuid),
INDEX (creation)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

Add an index on an exist table

1
CREATE INDEX ON product(creation);

Or use:

1
ALTER TABLE product ADD INDEX creation_index (creation);

Check index status

1
2
3
4
5
6
7
8
9
10
SHOW INDEX FROM product;

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user | 0 | PRIMARY | 1 | uuid | A | 103 | NULL | NULL | | BTREE | | | YES | NULL |
| user | 0 | uuid | 1 | uuid | A | 103 | NULL | NULL | | BTREE | | | YES | NULL |
| user | 1 | creation | 1 | creation | A | 103 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Test performance

The query that used to costs 52 seconds now return the result in 0.2 seconds, significant improvement.

Reference