Индекс MySQL для использования для тестов диапазона ⇐ MySql

[Anonymous]

У меня есть этот индекс: < /p>
create index main_cp_index on catalogue_product(
product_class_id, is_public,
(cast(coalesce(data->>'$."need_tags"', 0) as unsigned)) ASC);
< /code>
Когда я пытаюсь проверить наличие на наличие eund_tags 0 или 1, он используется: < /p>
mysql> explain SELECT count(*) FROM `catalogue_product` WHERE (product_class_id = 3 AND is_public = True AND cast(COALESCE(data->>'$."need_tags"', 0) as unsigned) = 1)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: catalogue_product
partitions: NULL
type: ref
possible_keys: catalogue_product_product_class_id_0c6c5b54_fk_catalogue,catalogue_product_is_public_1cf798c5,main_cp_index
key: main_cp_index
key_len: 14
ref: const,const,const
rows: 2740
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
< /code>
Но если попытка использовать в операторе или сравнение, индекс не используется: < /p>
mysql> explain SELECT count(*) FROM `catalogue_product` WHERE (product_class_id = 3 AND is_public = True AND cast(COALESCE(data->>'$."need_tags"', 0) as unsigned) in (0, 1))\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: catalogue_product
partitions: NULL
type: ref
possible_keys: catalogue_product_product_class_id_0c6c5b54_fk_catalogue,catalogue_product_is_public_1cf798c5,main_cp_index
key: catalogue_product_product_class_id_0c6c5b54_fk_catalogue
key_len: 5
ref: const
rows: 3471
filtered: 20.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT count(*) FROM `catalogue_product` WHERE (product_class_id = 3 AND is_public = True AND cast(COALESCE(data->>'$."need_tags"', 0) as unsigned) < 2)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: catalogue_product
partitions: NULL
type: ref
possible_keys: catalogue_product_product_class_id_0c6c5b54_fk_catalogue,catalogue_product_is_public_1cf798c5,main_cp_index
key: catalogue_product_product_class_id_0c6c5b54_fk_catalogue
key_len: 5
ref: const
rows: 3471
filtered: 33.33
Extra: Using where
1 row in set, 1 warning (0.00 sec)
< /code>
Можно ли каким -то образом использовать индекс здесь? Это часть более крупного запроса, поэтому я думаю, что мне нужно иметь какое -то пункт для ued_tags, хотя мне не волнует его значение, чтобы префикс индекса был в правильном порядке. < /P>
ps. Таблица такова: < /p>
mysql> show create table catalogue_product\G
*************************** 1. row ***************************
Table: catalogue_product
Create Table: CREATE TABLE `catalogue_product` (
`id` int NOT NULL AUTO_INCREMENT,
`structure` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL,
`upc` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci DEFAULT NULL,
`title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL,
`slug` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL,
`description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL,
`rating` double DEFAULT NULL,
`date_created` datetime(6) NOT NULL,
`date_updated` datetime(6) NOT NULL,
`is_discountable` tinyint(1) NOT NULL,
`parent_id` int DEFAULT NULL,
`product_class_id` int DEFAULT NULL,
`is_public` tinyint(1) NOT NULL,
`meta_description` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci,
`meta_title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci DEFAULT NULL,
`data` json NOT NULL DEFAULT (_utf8mb4'{}'),
`title_en` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci DEFAULT NULL,
`title_sl` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci DEFAULT NULL,
`nutrition` json NOT NULL DEFAULT (_utf8mb4'{}'),
`brand_id` int DEFAULT NULL,
`contents` json NOT NULL DEFAULT (_utf8mb4'{}'),
`allergens` json NOT NULL DEFAULT (_utf8mb4'[]'),
`description_en` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci,
`description_sl` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci,
`country` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_ci NOT NULL,
`priority` smallint NOT NULL,
`code` varchar(255) COLLATE utf8mb4_0900_as_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `upc` (`upc`),
UNIQUE KEY `code` (`code`),
KEY `catalogue_product_parent_id_9bfd2382_fk_catalogue_product_id` (`parent_id`),
KEY `catalogue_product_product_class_id_0c6c5b54_fk_catalogue` (`product_class_id`),
KEY `catalogue_product_slug_c8e2e2b9` (`slug`),
KEY `catalogue_product_date_updated_d3a1785d` (`date_updated`),
KEY `catalogue_product_date_created_d66f485a` (`date_created`),
KEY `catalogue_product_is_public_1cf798c5` (`is_public`),
KEY `catalogue_product_brand_id_74599134_fk_products_brand_id` (`brand_id`),
KEY `catalogue_product_priority_983a8f56` (`priority`),
KEY `need_tags_index` ((cast(json_extract(`data`,_utf8mb4'$."need_tags"') as char(5) charset utf8mb4))),
KEY `start_index` ((cast(json_extract(`data`,_utf8mb4'$."start"') as char(10) charset utf8mb4))),
KEY `touristic_index` ((cast(json_extract(`data`,_utf8mb4'$."touristic"') as char(2) charset utf8mb4))),
KEY `main_cp_index` (`product_class_id`,`is_public`,(cast(coalesce(json_unquote(json_extract(`data`,_utf8mb4'$."need_tags"')),0) as unsigned))),
CONSTRAINT `catalogue_product_brand_id_74599134_fk_products_brand_id` FOREIGN KEY (`brand_id`) REFERENCES `products_brand` (`id`),
CONSTRAINT `catalogue_product_parent_id_9bfd2382_fk_catalogue_product_id` FOREIGN KEY (`parent_id`) REFERENCES `catalogue_product` (`id`),
CONSTRAINT `catalogue_product_product_class_id_0c6c5b54_fk_catalogue` FOREIGN KEY (`product_class_id`) REFERENCES `catalogue_productclass` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18229 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_ci
< /code>
pps. Между тем же: < /p>
mysql> explain SELECT count(*) FROM `catalogue_product` WHERE (product_class_id = 3 AND is_public = True AND cast(COALESCE(data->>'$."need_tags"', 0) as unsigned) between 0 and 1)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: catalogue_product
partitions: NULL
type: ref
possible_keys: catalogue_product_product_class_id_0c6c5b54_fk_catalogue,catalogue_product_is_public_1cf798c5,main_cp_index
key: catalogue_product_product_class_id_0c6c5b54_fk_catalogue
key_len: 5
ref: const
rows: 3471
filtered: 11.11
Extra: Using where


Подробнее здесь: https://stackoverflow.com/questions/797 ... ange-tests