0

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 enter image description here

2
  • 1
    Have you tried to test with a table of a meaningful size? No sane optimizer will choose any index when accessing a one-row table.
    – mustaccio
    Jul 13, 2023 at 13:24
  • Get a thousand rows in the table. Change the 5000 to something that will match only a few percent of the table. Then it might decide that the index is worth using.
    – Rick James
    Jul 13, 2023 at 14:44

2 Answers 2

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

1
  • Thanks for your comment, but add a virtual column will change database schema, also code logic need to be changed as well (add a new field to map that virtual column). I just want to add an index to improve performance for existing query, without any code change. Jul 13, 2023 at 17:32
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.