MySQL: Reason for Degraded performance of a single inner join

Question

We have two tables in our MYSQL 5.7 Aurora database: CUSTOMER_ORDER and BATCH. Customer order can have only one batch associated and it is not mandatory to have one.

Create table statement of CUSTOMER_ORDER table:

CREATE TABLE 'CUSTOMER_ORDER' ( 'CLIENT_ID' varchar(32) COLLATE utf8mb4_bin NOT NULL, 'ORDER_ID' varchar(64) COLLATE utf8mb4_bin NOT NULL, 'ORDER' json NOT NULL, 'ORDER_DATE' date GENERATED ALWAYS AS 
(
   cast(json_unquote(json_extract('ORDER', '$.date')) as date)
)
VIRTUAL, 'TEAM_ID' varchar(32) COLLATE utf8mb4_bin GENERATED ALWAYS AS 
(
   json_unquote(json_extract('ORDER', '$.teamId.teamId'))
)
VIRTUAL, 'ORDER_SOURCE' varchar(32) COLLATE utf8mb4_bin GENERATED ALWAYS AS 
(
   json_unquote(json_extract('ORDER', '$.orderSource'))
)
VIRTUAL, 'ORDER_STATUS' varchar(32) COLLATE utf8mb4_bin GENERATED ALWAYS AS 
(
   json_unquote(json_extract('ORDER', '$.status.status'))
)
VIRTUAL, 'EFFECTIVE_STATUS' varchar(32) COLLATE utf8mb4_bin GENERATED ALWAYS AS 
(
   json_unquote(json_extract('ORDER', '$.effectiveStatus'))
)
VIRTUAL, 'CREATED_ON' timestamp(6) NOT NULL, 'UPDATED_ON' timestamp(6) NOT NULL, 'ADDED_ON' timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6) 
ON 
UPDATE
   CURRENT_TIMESTAMP(6), 'BATCH_ID' varchar(128) COLLATE utf8mb4_bin GENERATED ALWAYS AS 
   (
      json_unquote(json_extract('ORDER', '$.batchId.batchId'))
   )
   VIRTUAL, PRIMARY KEY ('CLIENT_ID', 'ORDER_ID'), KEY 'order_date_team_idx' ('CLIENT_ID', 'ORDER_DATE', 'TEAM_ID') ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

Create table statement for BATCH table:

    CREATE TABLE 'BATCH' ( 'CLIENT_ID' varchar(32) COLLATE utf8mb4_bin NOT NULL, 'BATCH_ID' varchar(128) COLLATE utf8mb4_bin NOT NULL, 'BATCH_DATE' date NOT NULL, 'BATCH_STATUS' varchar(32) COLLATE utf8mb4_bin NOT NULL, 'BATCH_SLA' varchar(32) COLLATE utf8mb4_bin NOT NULL, 'BATCH' json NOT NULL, 'EMPLOYEE_ID' varchar(32) COLLATE utf8mb4_bin DEFAULT NULL, 'EMPLOYEE_PERSONA_ID' varchar(32) COLLATE utf8mb4_bin DEFAULT NULL, 'VEHICLE_ID' varchar(32) COLLATE utf8mb4_bin DEFAULT NULL, 'VEHICLE_MODEL_ID' varchar(32) COLLATE utf8mb4_bin DEFAULT NULL, 'RECORD_VERSION' int(11) NOT NULL, 'CREATED_ON' timestamp(3) NOT NULL, 'UPDATED_ON' timestamp(3) NOT NULL, 'ADDED_ON' timestamp(3) NULL DEFAULT CURRENT_TIMESTAMP(3) 
ON
UPDATE
   CURRENT_TIMESTAMP(3), 'MINIMAL_BATCH' json DEFAULT NULL, 'BATCH_ID' varchar(64) COLLATE utf8mb4_bin GENERATED ALWAYS AS 
   (
      json_unquote(json_extract('MINIMAL_BATCH', '$.batch.planId.sourceId'))
   )
   VIRTUAL, 'PLAN_ID' varchar(64) COLLATE utf8mb4_bin GENERATED ALWAYS AS 
   (
      json_unquote(json_extract('MINIMAL_BATCH', '$.batch.planId.planId'))
   )
   VIRTUAL, PRIMARY KEY ('CLIENT_ID', 'BATCH_ID'), KEY 'date_rider_idx' ('CLIENT_ID', 'BATCH_DATE', 'EMPLOYEE_ID') ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin

And I am using the following query to find out the count of customer orders for a given client for a given date:

SELECT COUNT(1) 
FROM CUSTOMER_ORDER AS customer_order 
INNER JOIN BATCH AS batch 
 ON customer_order.CLIENT_ID = batch.CLIENT_ID 
 AND customer_order.BATCH_ID = batch.BATCH_ID 
WHERE 
 customer_order.CLIENT_ID = 'clientA' 
 AND ORDER_DATE = '2021-05-01';

The reason I am doing this left outer join is to do further filtering of customer orders based on the batch. The problem I am facing with this query is that it takes in order of minutes to execute this query for clients who have large number of customer orders(~20k-100k) for a given date even without any extra filters on the batch table.

The output of the EXPLAIN statement for the query is as given below:

id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,customer_order,NULL,ref,"PRIMARY,order_date_team_idx,batch_idx",PRIMARY,130,const,1,10.00,"Using where"
1,SIMPLE,batch,NULL,eq_ref,"PRIMARY,date_rider_idx,team_id_idx",PRIMARY,644,"const,locus_devo.customer_order.BATCH_ID",1,100.00,"Using index"

Can you please help me identify the root cause of underperformance of this query?

Answer 1

I think that the root of the problem is in AND ORDER_DATE = '2021-05-01' condition by VIRTUAL column which must be calculated for each table row. I'd recommend to make this column STORED.

If this won't improve enough then try:

1. Create an index CUSTOMER_ORDER (ORDER_DATE, CLIENT_ID, BATCH_ID) or CUSTOMER_ORDER (CLIENT_ID, ORDER_DATE, BATCH_ID).

2. Convert the query to

SELECT COUNT(1) 
FROM CUSTOMER_ORDER AS customer_order 
INNER JOIN BATCH AS batch ON customer_order.BATCH_ID = batch.BATCH_ID 
WHERE customer_order.CLIENT_ID = 'clientA' 
  AND batch.CLIENT_ID = 'clientA' 
  AND customer_order.ORDER_DATE = '2021-05-01';