sql.md

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name JSON NOT NULL
);   

INSERT INTO products (name) VALUES('{"en": "Traffic lights", "de": "Verkehrsampel", "nl": "Stoplichten"}');
INSERT INTO products (name) VALUES('{"en": "Car", "de": "Auto", "nl": "Wagen"}');
INSERT INTO products (name) VALUES('{"en": "Bike", "de": "Fahrrad", "nl": "Fietsen"}');
INSERT INTO products (name) VALUES('{"en": "Airplane", "de": "Flugzeug", "nl": "Vliegtuig"}');


ALTER TABLE products ADD name_en VARCHAR(30) AS (name->>"$.en");
ALTER TABLE products ADD name_de VARCHAR(30) AS (name->>"$.de");
ALTER TABLE products ADD name_nl VARCHAR(30) AS (name->>"$.nl");

ALTER TABLE products ADD INDEX `idx_name_en` (name_en);
ALTER TABLE products ADD INDEX `idx_name_de` (name_de);
ALTER TABLE products ADD INDEX `idx_name_nl` (name_nl);

---

No index usage 😭

mysql> explain select id, name_nl, name
    -> from products
    -> order by name_nl;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | products | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+----------------+

Efficient index usage, if all selected fields are in index

mysql> explain select id, name_nl
    -> from products
    -> order by name_nl;
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | products | NULL       | index | NULL          | idx_name_nl | 123     | NULL |    4 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1

Optimized for ordering with additional data

mysql> explain select product_data.*
    -> from products as product_sort
    -> left join products as product_data on (product_data.id = product_sort.id)
    -> order by product_sort.name_nl;
+----+-------------+--------------+------------+--------+---------------+-------------+---------+-----------------+------+----------+-------------+
| id | select_type | table        | partitions | type   | possible_keys | key         | key_len | ref             | rows | filtered | Extra       |
+----+-------------+--------------+------------+--------+---------------+-------------+---------+-----------------+------+----------+-------------+
|  1 | SIMPLE      | product_sort | NULL       | index  | NULL          | idx_name_nl | 123     | NULL            |    4 |   100.00 | Using index |
|  1 | SIMPLE      | product_data | NULL       | eq_ref | PRIMARY       | PRIMARY     | 4       | product_sort.id |    1 |   100.00 | NULL        |
+----+-------------+--------------+------------+--------+---------------+-------------+---------+-----------------+------+----------+-------------+