MySQL Forums
Forum List  »  Newbie

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.

Options: ReplyQuote


Subject
Written By
Posted
Query does not use index
April 26, 2021 03:02PM
April 27, 2021 08:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.