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.
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