Query does not use index
Posted by:
Phil Siyam
Date: April 26, 2021 03:02PM
Hi,
I am using MySQL Version 8.0.21 . There is big performance difference when I run query directly with where condition and if I use where on view created from the above query
Explain
SELECT
`cw`.`colorway_no` AS `colorway_no`,
GROUP_CONCAT(`cm`.`col_id`
ORDER BY `cw`.`col_no` ASC
SEPARATOR '--') AS `col_id_desc`
FROM
(`colorway_det` `cw`
JOIN `mas_color` `cm`)
WHERE
(`cw`.`col_id` = `cm`.`col_id`) and `cw`.`colorway_no` = '0102'
GROUP BY `cw`.`colorway_no`
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, cw, , ref, colorway_no, colorway_no, 16, const, 1, 100.00, Using index condition
1, SIMPLE, cm, , eq_ref, PRIMARY, PRIMARY, 2, cw.col_id, 1, 100.00, Using index
Explain
select * from vw_temp cw where `cw`.`colorway_no` = '0102'
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, PRIMARY, <derived2>, , ref, <auto_key0>, <auto_key0>, 16, const, 10, 100.00, Using where
2, DERIVED, cw, , ALL, colorway_no, , , , 55309, 100.00, Using filesort
2, DERIVED, cm, , eq_ref, PRIMARY, PRIMARY, 2, cw.col_id, 1, 100.00, Using index
I am actually using view in the 3rd query
select p.* from product p
join vw_temp v on p.colorway_no = v.colorway_no
But response is slow, so I changed it as below :
select p.* , v.col_id_desc from product p
join (SELECT
`cw`.`colorway_no` AS `colorway_no`,
GROUP_CONCAT(`cm`.`col_id`
ORDER BY `cw`.`col_no` ASC
SEPARATOR '--') AS `col_id_desc`
FROM
(`colorway_det` `cw`
JOIN `mas_color` `cm`)
WHERE
(`cw`.`col_id` = `cm`.`col_id`)
GROUP BY `cw`.`colorway_no`) v on p.colorway_no = v.colorway_no
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, PRIMARY, p, , ALL, , , , , 536, 100.00,
1, PRIMARY, <derived2>, , ref, <auto_key0>, <auto_key0>, 16, p.colorway_no, 103, 100.00,
2, DERIVED, cw, , ALL, colorway_no, , , , 55309, 100.00, Using filesort
2, DERIVED, cm, , eq_ref, PRIMARY, PRIMARY, 2, cw.col_id, 1, 100.00, Using index
It is still slow and explain shows that select_type is DERIVED for table cw
Thanks in advance.