MYSQL MAX batch value is slow

Hello, how are you? I'm white-headed to be in charge of my own MYSQL + Apache application server in a small company without any IT staff, especially Infra. ㅜ<

The problem is that I've been very slow on certain tasks for some time now, and I found that mysqld CPU is almost 100%. The cause is 20 minutes of sending data during the specific query below, and one of these consumes almost 20% of the resources.

The function that runs is called 'fn_get_max_batch_number', and the content seems simple to me. Of course, the last number in NAME_CONST('reqId',34246) keeps changing and sending data.

-- Connection Id: 19 -- -- User: root -- -- Host: localhost:50212 -- -- DB: bebs -- -- Command: Query -- -- Time: 515 -- -- State: Sending data

SELECT ifnull(MAX(t.batch_number),0) INTO Result FROM cb_ar_claim_detail t WHERE t.req_id= NAME_CONST('reqId',34246)

When I open the table, there are a lot of 34000 lines, but it seems like I am getting the maximum value of batch no, but is it taking so much time? Would it be helpful to see if it will be helpful... It's a bare roof.

Currently, if the process is filled, it will kill. I'm going crazy. Below is a representative query of the entire website calling this function. (There are 13 of them.))

<statement name="retrieveClaimApprLine">         
    <![CDATA[ 
    SELECT t.req_id,
           t.approval_seq,
           DATE_FORMAT(t.approval_date, '%d-%m-%Y') approval_date, 
           t.approval_status_code,
           fn_get_approval_status_name('claimApprStatus', t.approval_status_code)
              approval_status_name,
           t.approver_id,
           concat('[',
                  fn_get_group_info2('GN', t.approver_id, ''),
                  ']',
                  '<br>',
                  fn_get_group_info2('GI', t.approver_id, ''))
              appr_info,
           t.approval_reason,
           t.real_appr_id,
           fn_get_emp_info('NAME', t.real_appr_id) real_appr_name,
           fn_get_emp_info('EMAIL', t.real_appr_id) real_appr_mail,
           fn_get_emp_info('DEPTNAME', t.real_appr_id) real_appr_dept
      FROM cb_ar_claim_appr_line t
     WHERE t.req_id = ${reqId} AND t.approval_type = 'G'
     AND t.batch_number = fn_get_max_batch_number(${reqId})
    UNION ALL
      SELECT t.req_id,
             t.approval_seq,
             DATE_FORMAT(t.approval_date, '%d-%m-%Y') approval_date,
             t.approval_status_code,
             fn_get_approval_status_name('claimApprStatus', t.approval_status_code)
                approval_status_name,
             t.approver_id,
             concat(fn_get_emp_info('NAME', t.approver_id),'/',fn_get_emp_info('DEPTNAME', t.approver_id),'/',fn_get_emp_info('EMAIL', t.approver_id)) appr_info,
             t.approval_reason,
             t.real_appr_id,
             fn_get_emp_info('NAME', t.real_appr_id) real_appr_name,
             fn_get_emp_info('EMAIL', t.real_appr_id) real_appr_mail,
             fn_get_emp_info('DEPTNAME', t.real_appr_id) real_appr_dept
        FROM cb_ar_claim_appr_line t
       WHERE t.req_id = ${reqId} AND t.approval_type = 'E'
       AND t.batch_number = fn_get_max_batch_number(${reqId})
    ORDER BY approval_seq
    ]]>
</statement>

I ask you to bow your head for many guidance expeditions from masters.

mysql sending cpu

2022-09-22 11:42

1 Answers

Yes, thank you. By the way, the batch column is about 1 or 2 or 3 or 4, would it be helpful to intax?

2022-09-22 11:42

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656