MySQL: Reason for Degraded performance of a single inner join

0 votes
1 answer
159 views
                          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?



                        
Asked by PaulDaviesC (101 rep)
Jun 2, 2021, 06:41 AM
Last activity: Jul 15, 2025, 11:04 PM
MySQL: Reason for Degraded performance of a single inner join

Related Questions
