select final.grad_year_flag ,
final.registration_id ,
final.first_name ,
final.last_name ,
final.grad_year ,
final.photo_id ,
final.photo_type ,
final.photo_image_path
from
(
select B.grad_year_flag,
B.registration_id,
B.first_name,
B.last_name,
B.grad_year,
B.photo_id,
B.photo_type,
B.photo_image_path,
@rn1 := if(@grad_flag1=grad_year_flag, @rn1+1,1) as rn1,
@grad_flag1 := grad_year_flag
from
(
select A.*,
@rn := if(@grad_flag=grad_year_flag and @reg_flag=registration_id, @rn+1,1) as rn,
@grad_flag := grad_year_flag,
@reg_flag := registration_id
from
(
select distinct
CASE WHEN rk.grad_year= NAME_CONST(‘i_grad_year’,1975)THEN 0
ELSE rk.grad_year- NAME_CONST(‘i_grad_year’,1975)END grad_year_flag,
rpd.photo_type,
rpd.registration_id,
rpd.first_name,
rpd.last_name,
rk.grad_year,
rpd.photo_id,
rpd.photo_image_path
from db_cache.reg_k12_grad_year rk,
db_cache.reg_photo_details rpd
where rk.registration_id=rpd.registration_id
and rpd.photo_type in(2,3)
and rk.school_id = NAME_CONST(‘i_commId’,9183)and rk.grad_year between ( NAME_CONST(‘i_grad_year’,1975)-3) and ( NAME_CONST(‘i_grad_year’,1975)+3)
and rk.registration_id!= NAME_CONST(‘i_regId’,7490338311)order by grad_year_flag,rpd.registration_id,rpd.photo_type
) A
) B
where rn=1
ORDER BY grad_year_flag, rand()
)final
WHERE case when grad_year_flag = 0
then rn1 <= 15
else rn1 <=5
end ;
Explain plan from 5.6
±—±------------±--------------------±------±------------------------------------------------------±-----------------------±--------±---------------------------------------------±-----±---------±----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±--------------------±------±------------------------------------------------------±-----------------------±--------±---------------------------------------------±-----±---------±----------------------------------------------------------+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
| 2 | DERIVED | | ref | <auto_key0> | <auto_key0> | 9 | const | 10 | 100.00 | Using temporary; Using filesort |
| 3 | DERIVED | | ALL | NULL | NULL | NULL | NULL | 1755 | 100.00 | NULL |
| 4 | DERIVED | reg_k12_grad_year_b | range | PRIMARY,REG_K12_GRAD_YEAR_B_N1,REG_K12_GRAD_YEAR_B_N2 | REG_K12_GRAD_YEAR_B_N1 | 10 | NULL | 1755 | 100.00 | Using where; Using index; Using temporary; Using filesort |
| 4 | DERIVED | reg_photo_details_k | ref | reg_photo_details_k_n1 | reg_photo_details_k_n1 | 8 | db_cache.reg_k12_grad_year_b.registration_id | 1 | 100.00 | Using index condition; Distinct |
±—±------------±--------------------±------±------------------------------------------------------±-----------------------±--------±---------------------------------------------±-----±---------±----------------------------------------------------------+
Explain plan from 5.7
±—±------------±--------------------±-----------±-----±--------------±------------±--------±-------------------±---------±---------±---------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
±—±------------±--------------------±-----------±-----±--------------±------------±--------±-------------------±---------±---------±---------------------------------------------+
| 1 | PRIMARY | | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
| 2 | DERIVED | | NULL | ref | <auto_key0> | <auto_key0> | 9 | const | 10 | 100.00 | Using temporary; Using filesort |
| 3 | DERIVED | | NULL | ALL | NULL | NULL | NULL | NULL | 217730 | 100.00 | NULL |
| 4 | DERIVED | | NULL | ref | <auto_key0> | <auto_key0> | 8 | const | 907299 | 10.00 | Using where; Using temporary; Using filesort |
| 4 | DERIVED | | NULL | ref | <auto_key1> | <auto_key1> | 8 | rk.registration_id | 12 | 20.00 | Using where |
| 6 | DERIVED | reg_photo_details_i | NULL | ALL | NULL | NULL | NULL | NULL | 79803766 | 100.00 | NULL |
| 5 | DERIVED | reg_k12_grad_year_b | NULL | ALL | NULL | NULL | NULL | NULL | 90729926 | 100.00 | NULL |
±—±------------±--------------------±-----------±-----±--------------±------------±--------±-------------------±---------±---------±---------------------------------------------+
Same query took less than a sec on 5.6 and is taking 24 mins on 5.7, i tried recreating views on underlying table with ALGORITHM=MERGE but no change in explain plan, any help would be appreciated.