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
or
conditions in your join clauses are bad news, try and find a way of avoiding theminnodb_buffer_pool_size
and RAM size. Also,SHOW CREATE TABLE
for each table.