Help me in optimising the query which takes almost 50 seconds

Question

Please help me optimise the following query running on MySQL 8.0.28 Community Edition.

explain select id, 
        created_at, 
        data, 
        deleted_at, 
        entity_id, 
        entity_type, 
        updated_at, 
        version 
from items 
where JSON_UNQUOTE(JSON_EXTRACT(data, '$.invoiceNumber'))='MKNS22-23-000165' 
and JSON_UNQUOTE(JSON_EXTRACT(data, '$.poBarCode'))='ABCD-AJSJ130258-1' 
and (entity_type in ('GOT_TICK')) 
and (deleted_at is null or deleted_at >'2023-02-22 09:20:08.063');


+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+---------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys   | key             | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+---------+----------+-------------+
|  1 | SIMPLE      | items  | NULL       | ref  | entity_type_idx | entity_type_idx | 92      | const | 5381058 |    40.00 | Using where |
+----+-------------+--------+------------+------+-----------------+-----------------+---------+-------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


show indexes from items;
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| items |          0 | PRIMARY         |            1 | id          | A         |    10716855 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| items |          1 | entity_type_idx |            1 | entity_type | A         |         551 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| items |          1 | entity_id_idx   |            1 | entity_id   | A         |      204232 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)


 CREATE TABLE `items` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `entity_type` varchar(30) NOT NULL,
  `entity_id` varchar(30) NOT NULL,
  `data` json NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `version` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `entity_type_idx` (`entity_type`),
  KEY `entity_id_idx` (`entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20555956 DEFAULT CHARSET=utf8mb3 ;

I tried creating a composite index on deleted_at and entity_type and a separate index on deleted_at, but it did not help.

show indexes from items;
+-------+------------+---------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name                        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+---------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| items |          0 | PRIMARY                         |            1 | id          | A         |    13987955 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| items |          1 | entity_type_idx                 |            1 | entity_type | A         |         534 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| items |          1 | entity_id_idx                   |            1 | entity_id   | A         |      269627 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| items |          1 | deleted_at_idx                  |            1 | deleted_at  | A         |        9069 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| items |          1 | entity_type_deleted_at_comp_idx |            1 | entity_type | A         |         480 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| items |          1 | entity_type_deleted_at_comp_idx |            2 | deleted_at  | A         |       24518 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+---------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

If I understand it correctly, even if I create an index on data, it would not use that index. If I move the functions used in the where clause on the data column to the right of the equation, would that change anything?

The table size is 16 GB.

It's 16 core machine with CentOS 7 with 44GB RAM for MySQL 8.0.28 Community Edition.

Answer 1

To utilize indexes on deleted_at and entity_type, you need to rewrite your query to a union all query.

    select /*QB_2*/ items.id, items.created_at, items.data, items.deleted_at,
items.entity_id, items.entity_type, items.updated_at, items.version
from items
where JSON_UNQUOTE(JSON_EXTRACT(items.data, '$.invoiceNumber')) = 'MKNS22-23-000165'
  and JSON_UNQUOTE(JSON_EXTRACT(items.data, '$.poBarCode')) = 'ABCD-AJSJ130258-1'
  and items.entity_type in ('GOT_TICK')
  and items.deleted_at > '2023-02-22 09:20:08.063' 
union all
select /*QB_1*/ items.id, items.created_at, items.data, items.deleted_at,
items.entity_id, items.entity_type, items.updated_at, items.version
from items
where JSON_UNQUOTE(JSON_EXTRACT(items.data, '$.invoiceNumber')) = 'MKNS22-23-000165'
  and JSON_UNQUOTE(JSON_EXTRACT(items.data, '$.poBarCode')) = 'ABCD-AJSJ130258-1'
  and items.entity_type in ('GOT_TICK') and items.deleted_at is null

Here is the tuning details provide by my query tuning tool(PawSQL).