mysql query stuck in sending data state when execured from java client (JdbcTemplate)

When i am trying to run below sql query on mysql server then mysql server is going into sending data state for forever

SELECT a.cust
     , a.job_num
     , a.fund_num
     , a.fund_type
     , a.process_type
     , a.code
     , a.mail_type
     , a.rec_date
     , a.mail_date
     , a.acc_num
     , a.add1
     , a.add2
     , a.add3
     , a.add4
     , a.add5
     , a.add5
     , a.add7
     , a.tax_num
     , a.data_source
     , a.sec_dec
     , a.serv_flag
     , a.add_ind
     , a.cons_id
     , a.cust_num
     , a.cust_name
     , (CASE when a.process_type='DAILY' 
            THEN (SELECT b.roll_num 
                    FROM db_daily_report b 
                   WHERE b.acc_num = a.acc_num 
                     AND b.cust_num = a.cust_num 
                     AND b.cons_id = a.cons_id 
                     AND b.WRITTEN_TO = 'OUTPUT') 
            When a.PROCESS_TYPE='ANNUAL' 
            THEN (SELECT c.roll_num 
                    FROM db_annual_report c 
                   WHERE c.acc_num = a.acc_num 
                     AND c.cust_num = a.cust_num 
                     AND c.cons_id = a.cons_id 
                     AND c.WRITTEN_TO = 'OUTPUT') 
                END) roll_num
     , a.pref
     , a.cons_id
     , a.cntry 
  FROM audit_customer a  
 WHERE DATE(a.mail_date) BETWEEN '2020-10-05' and '2020-10-05' 
   AND a.process_type = 'Annual' 
   AND a.fund_type IN (2) 
   AND a.mail_type IN ('F') 
 ORDER 
    BY a.created_ts DESC

explain on above query returns following result

> id, select_type, table, type, possible_keys, key, key_len, ref, rows,
> Extra
>
> '1', 'PRIMARY', 'a', 'index_merge',
> 'idx_audit_customer_fund_type,idx_audit_customer_process_type,idx_audit_customer_rec_date',
> 'idx_audit_customer_rec_date,idx_audit_customer_fund_type,idx_audit_customer_process_type',
> '3,4,23', NULL, '196048', 'Using
> intersect(idx_audit_customer_rec_date,idx_audit_customer_fund_type,idx_audit_customer_process_type);
> Using where; Using filesort'
>
>
> '3', 'DEPENDENT SUBQUERY', 'c', 'ref',
> 'idx_db_annual_report_cust_name,idx_db_annual_report_acc_num,idx_db_annual_report_cons_id', 'idx_db_annual_report_acc_num', '63', 'cmhdb.a.acc_num', '16', 'Using
> where'
>
> '2', 'DEPENDENT SUBQUERY', 'b', 'ref',
> 'idx_db_daily_report_cust_name,idx_db_daily_report_acc_num,idx_db_daily_report_cons_id',
> 'idx_db_daily_report_acc_num', '63', 'db.a.acc_num', '16', 'Using
> where'

I am not able to understand why my query is stuck in sending_data state when I try to run

> SHOW_FULL_PROCESSLIST

command in mysql workbench

Your case/when construct is off... Your second WHEN is also using an alias "b", but the join condition is using alias "c" and probably throwing into a Cartesian result and choking on an every record to every record.

Also, not knowing your data, it will probably fail if either of those case/when select statements return more than one record.

FEEDBACK per request

Here is what I would change to. You are running based on annual, so I removed the case/when and just set to a direct JOIN condition to your annual table. If doing daily, I would have just change the table from the annual to your daily for the JOIN portion.

Also, to help optimize the query, I would have an index on (process_type, fund_type, mail_type, mail_date, created_ts )

The where clause with the date is done in two parts instead of between. Since you were using the date() function to strip off any time component of the record, that can not be optimized against an index. So, my index above puts the other fields in higher priority and moved the date to the tail-end. By doing a GREATER THAN or EQUAL to the '2020-10-05', you get everything on/after 12:00 midnight/morning of that date. By also getting AND LESS THAN '2020-10-06', you are getting everything for the 10/5 date up to 11:59:59PM for that day, hence less than 10/6. So now the index can be utilized inclusive of the date portion.

SELECT a.cust
     , a.job_num
     , a.fund_num
     , a.fund_type
     , a.process_type
     , a.code
     , a.mail_type
     , a.rec_date
     , a.mail_date
     , a.acc_num
     , a.add1
     , a.add2
     , a.add3
     , a.add4
     , a.add5
     , a.add5
     , a.add7
     , a.tax_num
     , a.data_source
     , a.sec_dec
     , a.serv_flag
     , a.add_ind
     , a.cons_id
     , a.cust_num
     , a.cust_name
     , c.roll_num
     , a.pref
     , a.cons_id
     , a.cntry 
  FROM 
     audit_customer a  
	    JOIN db_annual_report c 
		  on c.acc_num = a.acc_num 
         AND c.cust_num = a.cust_num 
         AND c.cons_id = a.cons_id 
         AND c.WRITTEN_TO = 'OUTPUT'

 WHERE 
	   a.process_type = 'Annual' 
   AND a.fund_type IN (2) 
   AND a.mail_type IN ('F') 
   AND a.mail_date >= '2020-10-05' 
   and a.mail_date < '2020-10-06' 
 ORDER 
    BY a.created_ts DESC

Feedback #2

To add clarification of >= and < vs between. Your original query was using the date() function call to get only the date portion from a date/time column. Using function calls in a where clause is not optimizable and can not be applied to an index.

So when you had date(mail_date) between '2020-10-05' and '2020-10-05', you were essentially asking for only activity for the single date of 2020-10-05 regardless of morning, afternoon, or late at night, as long as the date was 2020-10-05.

To take advantage of an index on a datetime field that could be any time within the day, I am not using the date() function, but allowing the full date/time field to be part of the where clause by doing the explicit >= and <

So my from date/time is 2020-10-05 at 12:00:00 am (midnight from 2020-10-04 changing to 2020-10-05). So, any activity as of the am even as early as 2020-10-05 12:00:01am (should activity be so early), would be included via the >= '2020-10-05'. By not explicitly stating the time, the 12 midnight (am) is the default.

Now, the ENDING date range, I am explicitly doing LESS THAN the 2020-10-06. This means it allows all the way up to 2020-10-05 to 11:59:59pm (just before midnight starting 2020-10-06).

If I did date( mail_date ) between '2020-10-05' and '2020-10-06', I would get activity for BOTH dates. Since you only cared about 1 day, the BETWEEN was not required and could have been done with date( mail_date ) = '2020-10-05' but would not have been optimized with an index either condition.

Does that help? I don't think I can explain it any further.