MySQL index to use for range tests

Question

I have this index:

create index main_cp_index on catalogue_product(
  product_class_id, is_public,
  (cast(coalesce(data->>'$."need_tags"', 0) as unsigned)) ASC);

When i'm trying to check for need_tags being 0 or 1, it's used:

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)

But if trying to use IN operator or comparison, the index is not used:

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)

Is it possible to somehow use the index here? It's a part of a bigger query, so I think I need to have some clause for need_tags even though I don't care about the value thereof, for the index prefix to be in the correct order.

PS. Table is this:

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

PPS. Between the same:

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

Maybe because there are a few rows the optimizer doesn't choose to use the index. By the way please run show create table catalogue_product and post the output on the question. — Ergest Basha
– Ergest Basha, Commented Jul 31 at 16:02
@ErgestBasha, on this test instance it's indeed around 4k, but main DB has around 150k. And regardless of the number of rows, it chooses to use it for simple comparisons? — aikipooh
– aikipooh, Commented Jul 31 at 16:21

ysth · Accepted Answer · 2025-07-31 22:24:49Z

2

When IN isn't using an index I want, I first try using a JOIN instead:

select ...
from (select 0 need_tags union all select 1) need_tags_values
join catalogue_product
    on cast(COALESCE(data->>'$."need_tags"', 0) as unsigned)=need_tags_values.need_tags
where product_class_id = 3 and
    is_public = True

answered Jul 31 at 22:24

ysth

99.1k6 gold badges126 silver badges219 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rick James Aug 10 at 0:02

Did this trick help??

O. Jones · Accepted Answer · 2025-07-31 19:45:38Z

1

MySQL and expression IN (this, that) don't get along real well, sorry to say.

Could you try an actual range like

cast(COALESCE(data->>'$."need_tags"', 0) as unsigned) BETWEEN 0 AND 1

Or maybe a UNION like

SELECT count(*) FROM (
  SELECT id `catalogue_product` 
   WHERE product_class_id = 3 
     AND is_public = True
     AND cast(COALESCE(data->>'$."need_tags"', 0) as unsigned) = 0 
  UNION ALL
  SELECT id `catalogue_product` 
   WHERE product_class_id = 3 
     AND is_public = True
     AND cast(COALESCE(data->>'$."need_tags"', 0) as unsigned) = 1
 ) ids

answered Jul 31 at 19:45

O. Jones

109k17 gold badges134 silver badges187 bronze badges

2 Comments

aikipooh Jul 31 at 20:10

Updated the post, BETWEEN doesn't help. UNION works, I was thinking about it. The problem is that in fact it's django that generates the query, and I've had hard time coercing it to make the correct query for the indices to work:) But well, I'll think some more tomorrow.

O. Jones Jul 31 at 21:10

You could try using a virtual (GENERATED ALWAYS AS) column for that JSON field, rather than the function index. Maybe would help? dev.mysql.com/doc/refman/8.4/en/…

Rick James · Accepted Answer · 2025-08-03 22:48:01Z

1

I don't know if this will help, but another thought is to start with

SELECT cast(COALESCE(data->>'$."need_tags"', 0) as unsigned) AS thing, count(*) AS ct
    FROM `catalogue_product` 
    WHERE product_class_id = 3 
      AND is_public = True
    GROUP BY 1;

Then

SELECT SUM(ct)
    FROM ( SELECT ... as above ... )
    WHERE thing <= 1;

answered Aug 3 at 22:48

Rick James

144k15 gold badges144 silver badges255 bronze badges

1 Comment

aikipooh Aug 5 at 12:10

Thanks! Interesting enough, <2 didn't use the index, but <=1 seems to use it!

Collectives™ on Stack Overflow

MySQL index to use for range tests

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related