0

Db is on Aurora cluster. Added indexes. removed functions from where clause. rewrote some logic. Got number of records to measurable limit, but still the query is performing slow. Attaching query plan below. Any thoughts and what else i can look into. It performs ok while testing for a use case, but for 250M records output its not finishing. CPU is barely used.

DROP TABLE IF EXISTS test_segments;
CREATE TABLE test_segments 
Select s.`year_id` as ryear
            ,u.`display_name`
            ,u.`user_email`
            ,u.`user_login`
            ,p.`date`
            ,p.`month`
            ,p.`day`
            ,p.`time`
            ,p.`i_time_start` i_time_start
            ,p.`i_time_end` i_time_end
            ,(CASE WHEN p.`i_time_end` > (p.`time` + interval p.`duration` minute) + 15 THEN MOD(p.`duration`, 15) ELSE 15 END) AS interval_duration
            ,IFNULL(p.`duration`, rp.`duration`) as total_duration
--          ,(CASE WHEN p.`duration` IS NULL THEN rp.`duration` ELSE p.`duration` END) as total_duration
            ,p.`zone`
            ,p.`station`
            ,p.nola_code
            ,p.`title`
            ,cl.`percentage`
            ,cl.`company`
            ,p.`vf` 
            ,c.`tds`
            ,p.`vf` * (CASE WHEN p.`i_time_end` > (p.`time` + interval IFNULL(p.`duration`, rp.`duration`) minute) + 15 
                              THEN MOD(IFNULL(p.`duration`, rp.`duration`) , 15) ELSE 15 END) * c.`tds` 
        from `test_progvf` p 
 INNER JOIN `abs_org_production`.`wabs_prtl_rp` rp
                ON 
              ( rp.`season`= 0 and p.smalltitle= rp.smalltitle )
             or       ( rp.`season`<> 0 and rp.episode in ('0', '000000') and p.smalltitle = rp.smalltitle )
              or      ( rp.`season`<> 0 and rp.episode = p.nola_code )

              INNER JOIN `test_report_cd` c
            ON
                c.`call_sign` = p.`station` 
                AND p.`year` = c.`year` 
                AND (p.`month_num`<=6 and c.`month`=1 OR p.`month_num` >6 and c.`month`=2)
                  INNER JOIN `abs_org_production`.`wabs_prtl_rys` s
                ON rp.`ryear_id` = s.`id`
        INNER JOIN `abs_org_production`.`wabs_users` u
                ON rp.`user_id` = u.`id`
        INNER JOIN `abs_org_production`.`wabs_prtl_cl` cl
                ON rp.`id` = cl.`rp_id`
        WHERE  (s.`status` = 'pending_approval' )
            AND c.`tds` > 0 

enter image description here

3
  • You’ve not attached the plan. Blind guessing against the plain SQL without knowing anything about your data model: the or conditions in your join clauses are bad news, try and find a way of avoiding them Jun 11, 2022 at 11:19
  • Hi Andrew, thanks I have attached the plan now. I changed the case statements to those OR's, but that logic is legacy logic so not sure how I can change the ors. Would keeping the case statements be ok, I read somewhere that case statements dont allow mysql to use indexes on columns in stmt? Thanks!
    – Sddesh
    Jun 13, 2022 at 15:07
  • Please provide value of innodb_buffer_pool_size and RAM size. Also, SHOW CREATE TABLE for each table.
    – Rick James
    Jun 21, 2022 at 3:41

1 Answer 1

0

Some of these indexes may help:

s:  INDEX(status, id,  year_id)
p:  INDEX(station, year)
cl:  INDEX(rp_id,  percentage, company)
c:  INDEX(tds, call_sign,  year, month)
c:  INDEX(call_sign,  year, month, tds)

When adding a composite index, DROP index(es) with the same leading columns. That is, when you have both INDEX(a) and INDEX(a,b), toss the former.

Please provide the text version of EXPLAIN SELECT ...

OR inhibits Optimization in ON and WHERE. So, if you can find a way to get rid of them, it might help a lot. (It would also require revisiting the INDEXes.)

Similarly, functions (including CASE) inhibits Optimization in ON and WHERE. (In the SELECT clause, functions are fine.)

Sometimes using UNION is an alternative to OR, but that would be quite messy here.

Another formulation for total_duration might be COALESCE(p.duration, rp.duration)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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