MySQL Forums
Forum List  »  Newbie

Re: Query does not use index
Posted by: Phil Siyam
Date: February 04, 2022 09:38AM

Peter when I rewrite query as

select MP.product_id, MP.style_id, MP.colorway_no, MP.design_id, MP.row_status, ch.colorway_id, ch.colorway_no
, GROUP_CONCAT( cm.col_name ORDER BY cw.col_no ASC SEPARATOR '/' ) colorway_desc
from mas_product MP
join colorway_h ch on ch.colorway_no = MP.colorway_no
JOIN colorway_det cw ON ch.colorway_id = cw.colorway_h_id
JOIN mas_color cm ON cw.col_id = cm.col_id
WHERE MP.product_id IN ('15298','15300','15296','15514')
GROUP BY MP.product_id, MP.style_id, MP.colorway_no, MP.design_id, MP.row_status, ch.colorway_id, ch.colorway_no


Explain Result set ===>

******************** 1. row *********************
id: 1
select_type: SIMPLE
table: MP
partitions:
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref:
rows: 4
filtered: 100.00
Extra: Using where; Using filesort
******************** 2. row *********************
id: 1
select_type: SIMPLE
table: ch
partitions:
type: eq_ref
possible_keys: PRIMARY,colorway_no_UNIQUE
key: colorway_no_UNIQUE
key_len: 16
ref: plm_lineup_db.MP.colorway_no
rows: 1
filtered: 100.00
Extra: Using index
******************** 3. row *********************
id: 1
select_type: SIMPLE
table: cw
partitions:
type: ref
possible_keys: colorway_id,col_id
key: colorway_id
key_len: 4
ref: plm_lineup_db.ch.colorway_id
rows: 1
filtered: 100.00
Extra:
******************** 4. row *********************
id: 1
select_type: SIMPLE
table: cm
partitions:
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: plm_lineup_db.cw.col_id
rows: 1
filtered: 100.00
Extra:
4 rows in set

This execution plan is what I am trying to achieve but using subquery. I cannot group my main query like this because there are other tables involved in join and those table have further subqueries.

Options: ReplyQuote


Subject
Written By
Posted
February 02, 2022 04:48PM
February 03, 2022 11:57AM
February 03, 2022 04:31PM
February 03, 2022 04:54PM
February 03, 2022 05:17PM
February 03, 2022 05:40PM
February 03, 2022 08:11PM
Re: Query does not use index
February 04, 2022 09:38AM
February 04, 2022 11:03AM
February 04, 2022 05:18AM
February 04, 2022 09:29AM
February 04, 2022 09:35AM


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.