MySQL 8 is not indexing on Functional index

Question

I'm using MySQL 8.0.30 and known that it's supporting for Functional index. However I tried to create an index base on sum of some fields, it's very easy as below, but index is not used, even in possible indexes, it's not showed as well.

Here is my table

CREATE TABLE `sample_product` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `avai_qty` double DEFAULT NULL,
  `picked_qty` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `functional_index` (((`avai_qty` + `picked_qty`)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

And here is my query

select * from sample_product where avai_qty + picked_qty > 5000;

I tried to run explain

{
    "query_block": {
        "cost_info": {
            "query_cost": "0.35"
        },
        "select_id": 1,
        "table": {
            "access_type": "ALL",
            "attached_condition": "((`sample_product`.`avai_qty` + `sample_product`.`picked_qty`) > 5000)",
            "cost_info": {
                "data_read_per_join": "1K",
                "eval_cost": "0.10",
                "prefix_cost": "0.35",
                "read_cost": "0.25"
            },
            "filtered": "100.00",
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 1,
            "table_name": "sample_product",
            "used_columns": [
                "id",
                "name",
                "avai_qty",
                "picked_qty"
            ]
        }
    }
}

I'm not sure if it's MySQL's bug or I did something wrong.

I have tried to show index and here is result, it has functional index as I defined

Answer 1

MySQL will not use index when the table is almost empty.

MySQL cannot use expressional index when this expression is used in the condition, even when the expression in the index and in the condition matches literally.

Possible solution: create virtual generated column using needed expression, create index by this column, use this column in the condition instead of the expression. Make this column invisible - if not then the queries which are columnslist-critical (for example, INSERT INTO table SELECT ..) will fail after new column creation.

CREATE TABLE `sample_product` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `avai_qty` double DEFAULT NULL,
  `picked_qty` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `functional_index` (((`avai_qty` + `picked_qty`)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO sample_product
WITH RECURSIVE cte AS (
  SELECT 1 id UNION ALL
  SELECT id+1 FROM cte LIMIT 1000
)
SELECT id, '', RAND() * 3000, RAND() * 3000
FROM cte;

EXPLAIN
select * from sample_product where (avai_qty + picked_qty) > 5000;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	sample_product	null	ALL	null	null	null	null	1000	100.00	Using where

ALTER TABLE sample_product
  DROP INDEX functional_index,
  ADD COLUMN sum_qty DOUBLE AS (`avai_qty` + `picked_qty`) VIRTUAL INVISIBLE,
  ADD INDEX functional_index (sum_qty);
ANALYZE TABLE sample_product;

EXPLAIN
select * from sample_product where sum_qty > 5000;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	sample_product	null	range	functional_index	functional_index	9	null	60	100.00	Using where

fiddle

Answer 2

Finally, I changed type of 2 number columns to bigint instead of double, and try again that query, it worked as expected.

CREATE TABLE `sample_product_bigint` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `avai_qty` bigint DEFAULT NULL,
  `picked_qty` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `functional_index` (((`avai_qty` + `picked_qty`)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

explain select * from sample_product_bigint where avai_qty + picked_qty > 5000;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	sample_product_bigint		range	functional_index	functional_index	9		1	100.00	Using where