Yes, we have see performance issue. Please see below vertical explain output. I am concerned about 3. row which has select_type: DERIVED and ref blank
******************** 1. row *********************
id: 1
select_type: PRIMARY
table: MP
partitions:
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref:
rows: 3
filtered: 100.00
Extra: Using where
******************** 2. row *********************
id: 1
select_type: PRIMARY
table: <derived2>
partitions:
type: ref
possible_keys: <auto_key0>
key: <auto_key0>
key_len: 16
ref: plm_lineup_db.MP.colorway_no
rows: 10
filtered: 100.00
Extra:
******************** 3. row *********************
id: 2
select_type: DERIVED
table: ch
partitions:
type: index
possible_keys: PRIMARY,colorway_no_UNIQUE
key: colorway_no_UNIQUE
key_len: 16
ref:
rows: 60950
filtered: 100.00
Extra: Using index; Using filesort
******************** 4. row *********************
id: 2
select_type: DERIVED
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:
******************** 5. row *********************
id: 2
select_type: DERIVED
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:
5 rows in set
====================================Json
-> Nested loop inner join (cost=25852.53 rows=258437)
-> Filter: (MP.product_id in ('2000215298','2000215300','2000215296')) (cost=1.36 rows=3)
-> Index range scan on MP using PRIMARY (cost=1.36 rows=3)
-> Index lookup on A using <auto_key0> (colorway_no=MP.colorway_no)
-> Materialize (cost=83682.24..83682.24 rows=86146)
-> Group aggregate: group_concat(cm.col_name order by cw.col_no ASC separator '/') (cost=75067.69 rows=86146)
-> Nested loop inner join (cost=66453.13 rows=86146)
-> Nested loop inner join (cost=36302.19 rows=86146)
-> Sort: ch.colorway_id, ch.colorway_no (cost=6151.25 rows=60950)
-> Index scan on ch using colorway_no_UNIQUE
-> Index lookup on cw using colorway_id (colorway_h_id=ch.colorway_id) (cost=0.35 rows=1)
-> Single-row index lookup on cm using PRIMARY (col_id=cw.col_id) (cost=0.25 rows=1)