Not the answer you need?
Register and ask your own question!

After upgrading to MySQL 5.7 queries on Views not using underlying indexes of tables

jayakarthikjayakarthik EntrantCurrent User Role Beginner
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 | <derived2> | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
| 2 | DERIVED | <derived3> | ref | <auto_key0> | <auto_key0> | 9 | const | 10 | 100.00 | Using temporary; Using filesort |
| 3 | DERIVED | <derived4> | 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 | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
| 2 | DERIVED | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 9 | const | 10 | 100.00 | Using temporary; Using filesort |
| 3 | DERIVED | <derived4> | NULL | ALL | NULL | NULL | NULL | NULL | 217730 | 100.00 | NULL |
| 4 | DERIVED | <derived5> | NULL | ref | <auto_key0> | <auto_key0> | 8 | const | 907299 | 10.00 | Using where; Using temporary; Using filesort |
| 4 | DERIVED | <derived6> | 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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.