Query time 0.00257
JSON explain
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "215.87"
},
"ordering_operation": {
"using_temporary_table": true,
"using_filesort": true,
"nested_loop": [
{
"table": {
"table_name": "v",
"access_type": "range",
"possible_keys": [
"PRIMARY",
"fl",
"variant_id",
"lang_code",
"product_id",
"fpl"
],
"key": "product_id",
"used_key_parts": [
"product_id"
],
"key_length": "3",
"rows_examined_per_scan": 90,
"rows_produced_per_join": 50,
"filtered": "56.37",
"index_condition": "(`portal`.`v`.`product_id` in (41125,33159,33160,33161,33164,33165))",
"cost_info": {
"read_cost": "121.86",
"eval_cost": "10.15",
"prefix_cost": "132.01",
"data_read_per_join": "39K"
},
"used_columns": [
"feature_id",
"product_id",
"variant_id",
"value",
"value_int",
"lang_code"
],
"attached_condition": "((`portal`.`v`.`lang_code` = 'en') and (`portal`.`v`.`feature_id` is not null))"
}
},
{
"table": {
"table_name": "f",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"status"
],
"key": "PRIMARY",
"used_key_parts": [
"feature_id"
],
"key_length": "3",
"ref": [
"portal.v.feature_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 4,
"filtered": "9.44",
"cost_info": {
"read_cost": "50.74",
"eval_cost": "0.96",
"prefix_cost": "192.89",
"data_read_per_join": "2K"
},
"used_columns": [
"feature_id",
"feature_type",
"categories_path",
"parent_id",
"display_on_catalog",
"status",
"position"
],
"attached_condition": "((`portal`.`f`.`status` = 'A') and (`portal`.`f`.`display_on_catalog` = 'Y') and ((`portal`.`f`.`categories_path` = '') or find_in_set(12,`portal`.`f`.`categories_path`) or find_in_set(95,`portal`.`f`.`categories_path`) or find_in_set(97,`portal`.`f`.`categories_path`)) and (if(`portal`.`f`.`parent_id`,(/* select#2 */ select `portal`.`df`.`status` from `portal`.`q_product_features` `df` where (`portal`.`df`.`feature_id` = `portal`.`f`.`parent_id`)),'A') = 'A') and ((`portal`.`v`.`variant_id` <> 0) or ((`portal`.`f`.`feature_type` <> 'C') and (`portal`.`v`.`value` <> '')) or (`portal`.`f`.`feature_type` = 'C') or (`portal`.`v`.`value_int` <> '')))",
"attached_subqueries": [
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "1.20"
},
"table": {
"table_name": "df",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"feature_id"
],
"key_length": "3",
"ref": [
"portal.f.parent_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.20",
"prefix_cost": "1.20",
"data_read_per_join": "432"
},
"used_columns": [
"feature_id",
"status"
]
}
}
}
]
}
},
{
"table": {
"table_name": "fd",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"feature_id",
"lang_code"
],
"key_length": "9",
"ref": [
"portal.v.feature_id",
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 4,
"filtered": "100.00",
"cost_info": {
"read_cost": "4.79",
"eval_cost": "0.96",
"prefix_cost": "198.64",
"data_read_per_join": "7K"
},
"used_columns": [
"feature_id",
"description",
"prefix",
"suffix",
"lang_code"
]
}
},
{
"table": {
"table_name": "fv",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"variant_id"
],
"key_length": "3",
"ref": [
"portal.v.variant_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 4,
"filtered": "100.00",
"cost_info": {
"read_cost": "4.79",
"eval_cost": "0.96",
"prefix_cost": "204.39",
"data_read_per_join": "5K"
},
"used_columns": [
"variant_id",
"position"
]
}
},
{
"table": {
"table_name": "vd",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"variant_id",
"lang_code"
],
"key_length": "9",
"ref": [
"portal.fv.variant_id",
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 4,
"filtered": "100.00",
"cost_info": {
"read_cost": "4.79",
"eval_cost": "0.96",
"prefix_cost": "210.13",
"data_read_per_join": "14K"
},
"used_columns": [
"variant_id",
"variant",
"lang_code"
]
}
},
{
"table": {
"table_name": "gf",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"feature_id"
],
"key_length": "3",
"ref": [
"portal.f.parent_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 4,
"filtered": "100.00",
"cost_info": {
"read_cost": "4.79",
"eval_cost": "0.96",
"prefix_cost": "215.88",
"data_read_per_join": "2K"
},
"used_columns": [
"feature_id",
"feature_type",
"position"
],
"attached_condition": "<if>(is_not_null_compl(gf), (`portal`.`gf`.`feature_type` = 'G'), true)"
}
}
]
}
}
}
Result
product_id |
feature_id |
value |
value_int |
variant_id |
feature_type |
description |
prefix |
parent_id |
position |
gposition |
variant |
suffix |
33161 |
61 |
|
|
8517 |
S |
Band Color |
|
60 |
1 |
9 |
Gold |
|
33164 |
61 |
|
0.00 |
8514 |
S |
Band Color |
|
60 |
1 |
9 |
Silver |
|
33159 |
61 |
|
0.00 |
9660 |
S |
Band Color |
|
60 |
1 |
9 |
|
|
33165 |
61 |
|
0.00 |
8514 |
S |
Band Color |
|
60 |
1 |
9 |
Silver |
|
33160 |
61 |
|
0.00 |
8514 |
S |
Band Color |
|
60 |
1 |
9 |
Silver |
|
33161 |
62 |
|
|
8504 |
S |
Band Material |
|
60 |
2 |
9 |
Stainless steel |
|
33164 |
62 |
|
|
8504 |
S |
Band Material |
|
60 |
2 |
9 |
Stainless steel |
|
33159 |
62 |
|
|
8504 |
S |
Band Material |
|
60 |
2 |
9 |
Stainless steel |
|
33165 |
62 |
|
|
8504 |
S |
Band Material |
|
60 |
2 |
9 |
Stainless steel |
|
33159 |
2 |
|
|
5962 |
E |
Brand |
|
1 |
0 |
1 |
Spectrum |
|
33165 |
2 |
|
|
5962 |
E |
Brand |
|
1 |
0 |
1 |
Spectrum |
|
33160 |
2 |
|
|
5962 |
E |
Brand |
|
1 |
0 |
1 |
Spectrum |
|
33161 |
2 |
|
|
5962 |
E |
Brand |
|
1 |
0 |
1 |
Spectrum |
|
41125 |
2 |
|
|
7478 |
E |
Brand |
|
1 |
0 |
1 |
Daniel Klein |
|
33164 |
2 |
|
|
5962 |
E |
Brand |
|
1 |
0 |
1 |
Spectrum |
|
41125 |
56 |
|
|
6058 |
S |
Condition |
|
1 |
3 |
1 |
New |
|
33160 |
56 |
|
|
6058 |
S |
Condition |
|
1 |
3 |
1 |
New |
|
33165 |
56 |
|
|
6058 |
S |
Condition |
|
1 |
3 |
1 |
New |
|
33164 |
56 |
|
|
6058 |
S |
Condition |
|
1 |
3 |
1 |
New |
|
33159 |
56 |
|
|
6058 |
S |
Condition |
|
1 |
3 |
1 |
New |
|
33164 |
6 |
|
|
1 |
S |
Department |
|
1 |
6 |
1 |
Men |
|
41125 |
6 |
|
|
1 |
S |
Department |
|
1 |
6 |
1 |
Men |
|
33159 |
6 |
|
|
1 |
S |
Department |
|
1 |
6 |
1 |
Men |
|
33165 |
6 |
|
|
1 |
S |
Department |
|
1 |
6 |
1 |
Men |
|
33160 |
6 |
|
|
1 |
S |
Department |
|
1 |
6 |
1 |
Men |
|
33161 |
6 |
|
|
1 |
S |
Department |
|
1 |
6 |
1 |
Men |
|
33161 |
65 |
|
|
6224 |
S |
Dial Face Color |
|
60 |
5 |
9 |
White |
|
33160 |
65 |
|
|
6188 |
S |
Dial Face Color |
|
60 |
5 |
9 |
Black |
|
33164 |
65 |
|
|
6224 |
S |
Dial Face Color |
|
60 |
5 |
9 |
White |
|
33165 |
65 |
|
|
6224 |
S |
Dial Face Color |
|
60 |
5 |
9 |
White |
|
33164 |
63 |
|
|
6061 |
S |
Dial Face Type |
|
60 |
3 |
9 |
Analog |
|
33159 |
63 |
|
|
6061 |
S |
Dial Face Type |
|
60 |
3 |
9 |
Analog |
|
33165 |
63 |
|
|
6061 |
S |
Dial Face Type |
|
60 |
3 |
9 |
Analog |
|
33161 |
63 |
|
|
6061 |
S |
Dial Face Type |
|
60 |
3 |
9 |
Analog |
|
33160 |
63 |
|
|
6061 |
S |
Dial Face Type |
|
60 |
3 |
9 |
Analog |
|
33164 |
64 |
|
|
8512 |
S |
Dial Material |
|
60 |
4 |
9 |
Stainless Steel |
|
33159 |
64 |
|
|
8512 |
S |
Dial Material |
|
60 |
4 |
9 |
Stainless Steel |
|
33160 |
64 |
|
|
8512 |
S |
Dial Material |
|
60 |
4 |
9 |
Stainless Steel |
|
33165 |
64 |
|
|
8512 |
S |
Dial Material |
|
60 |
4 |
9 |
Stainless Steel |
|
33161 |
64 |
|
|
8512 |
S |
Dial Material |
|
60 |
4 |
9 |
Stainless Steel |
|
33160 |
66 |
|
|
6071 |
S |
Watch Movement |
|
60 |
6 |
9 |
Swiss Precision |
|
33165 |
66 |
|
|
6071 |
S |
Watch Movement |
|
60 |
6 |
9 |
Swiss Precision |
|
33161 |
66 |
|
|
6071 |
S |
Watch Movement |
|
60 |
6 |
9 |
Swiss Precision |
|
33164 |
66 |
|
|
6071 |
S |
Watch Movement |
|
60 |
6 |
9 |
Swiss Precision |
|
33159 |
66 |
|
|
6071 |
S |
Watch Movement |
|
60 |
6 |
9 |
Swiss Precision |
|