0

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?

2
  • There seem to be two 'BATCH_ID' columns in BATCH?? – Rick James Jun 2 at 19:03
  • Please provide EXPLAIN FORMAT=JSON SELECT .... It may add info to the discussion between Akina and PaulDavesC. – Rick James Jun 2 at 19:48
0

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';
5
  • 'AND ORDER_DATE = '2021-05-01'' may not be a problem considering that we have an index on ('CLIENT_ID', 'ORDER_DATE', 'TEAM_ID') – PaulDaviesC Jun 2 at 9:45
  • @PaulDaviesC See EXPLAIN. PRIMARY index is used for this table, and RM claims that server cannot use 2 indices for the same table copy in a query. If the value will be extracted from another index nevertheless (I do not see the guarantee for this) then excess work for the value extraction decreases the performance, is it? Try to ignore (or maybe remove temporarily) this index - does the execution time will increase? – Akina Jun 2 at 10:09
  • The EXPLAIN says eq_ref and "const,locus_devo.customer_order.BATCH_ID". This sounds like both columns of the PK are being used. – Rick James Jun 2 at 19:50
  • @RickJames 1) PK does not include column ORDER_DATE 2) This sounds like "use const index seek for CLIENT_ID and index scan for BATCH_ID". – Akina Jun 2 at 19:54
  • But note that it starts with the other table, then reaches into BATCH. – Rick James Jun 2 at 21:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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