Długość indeksu varchar MySQL
Mam taki stół:
CREATE TABLE `products` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(512) NOT NULL,
`description` text,
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8;
i jeden taki:
CREATE TABLE `product_variants` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(11) unsigned NOT NULL,
`product_code` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `product_code` (`product_code`),
KEY `product_variant_product_fk` (`product_id`),
CONSTRAINT `product_variant_product_fk` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1037 DEFAULT CHARSET=utf8;
i takie instrukcje SQL
SELECT p.id AS id, p.name AS name, p.description AS description, pv.id AS product_variant_id, pv.product_code AS product_code
FROM products p
INNER JOIN product_variants pv ON pv.product_id = p.id
ORDER BY p.name ASC
LIMIT 300 OFFSET 0;
co, jeśli wyjaśnię, daje mi to:
+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
| 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 993658 | Using filesort |
| 1 | SIMPLE | pv | ref | product_variant_product_fk | product_variant_product_fk | 4 | db.p.id | 1 | |
+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
2 rows in set (0.00 sec)
Na milion wierszy jest to dość powolne. Próbowałem dodać indeks na products.name za pomocą:
ALTER TABLE products ADD INDEX `product_name_idx` (name(512));
co daje to:
mysql> show indexes from products;
+----------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| products | 0 | PRIMARY | 1 | id | A | 993658 | NULL | NULL | | BTREE | | |
| products | 1 | product_manf_fk | 1 | manufacturer_id | A | 18 | NULL | NULL | YES | BTREE | | |
| products | 1 | product_name_idx | 1 | name | A | 201 | 255 | NULL | | BTREE | | |
+----------+------------+------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
Myślę, że kolumna Sub_part pokazuje prefiks, który był indeksowany (w bajtach), jak opisano wta strona.
Po ponownym wyjaśnieniu zapytania otrzymuję:
+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
| 1 | SIMPLE | p | ALL | PRIMARY | NULL | NULL | NULL | 993658 | Using filesort |
| 1 | SIMPLE | pv | ref | product_variant_product_fk | product_variant_product_fk | 4 | db.p.id | 1 | |
+----+-------------+-------+------+----------------------------+----------------------------+---------+---------+--------+----------------+
2 rows in set (0.00 sec)
który wygląda tak, jakby nowy indeks nie był używany. Jak opisano nata strona, indeksy nie będą używane do sortowania, jeśli są indeksami prefiksu. W rzeczywistości, jeśli obcię dane za pomocą:
alter table products modify `name` varchar(255) not null;
Wyjaśnienie daje:
+----+-------------+-------+-------+----------------------------+----------------------------+---------+----------------------------------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------------------+----------------------------+---------+----------------------------------------------+------+-------+
| 1 | SIMPLE | p | index | PRIMARY | product_name_idx | 767 | NULL | 300 | |
| 1 | SIMPLE | pv | ref | product_variant_product_fk | product_variant_product_fk | 4 | oh_2c98c233_69fe_4f06_ad0d_fe6f85a5beac.p.id | 1 | |
+----+-------------+-------+-------+----------------------------+----------------------------+---------+----------------------------------------------+------+-------+
co myślę, że to popiera. Jednak mówi dalejta strona że tabele InnoDB mogą mieć do 767 bajtów indeksu. Jeśli długość jest w bajtach, dlaczego nie chce mieć więcej niż 255? Jeśli jest w postaci, jak decyduje długość każdej postaci UTF-8? Czy to tylko przy założeniu 3?
Korzystam również z tej wersji MySQL:
mysql> select version();
+------------+
| version() |
+------------+
| 5.5.27-log |
+------------+
1 row in set (0.00 sec)