Query time 0.01445
JSON explain
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "403.43"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": true,
"cost_info": {
"sort_cost": "39.93"
},
"nested_loop": [
{
"table": {
"table_name": "inventory",
"access_type": "system",
"possible_keys": [
"pc"
],
"rows_examined_per_scan": 0,
"rows_produced_per_join": 1,
"filtered": "0.00",
"const_row_not_found": true,
"cost_info": {
"read_cost": "0.00",
"eval_cost": "0.20",
"prefix_cost": "0.00",
"data_read_per_join": "976"
},
"used_columns": [
"product_id",
"amount"
]
}
},
{
"table": {
"table_name": "q_categories",
"access_type": "const",
"possible_keys": [
"PRIMARY",
"c_status",
"p_category_id"
],
"key": "PRIMARY",
"used_key_parts": [
"category_id"
],
"key_length": "3",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "0.20",
"prefix_cost": "0.00",
"data_read_per_join": "2K"
},
"used_columns": [
"category_id",
"usergroup_ids",
"status"
]
}
},
{
"table": {
"table_name": "q_product_filters",
"access_type": "range",
"possible_keys": [
"PRIMARY",
"feature_id"
],
"key": "feature_id",
"used_key_parts": [
"feature_id"
],
"key_length": "3",
"rows_examined_per_scan": 2,
"rows_produced_per_join": 1,
"filtered": "75.00",
"index_condition": "((`portal`.`q_product_filters`.`feature_id` in (2,7)) and (`portal`.`q_product_filters`.`feature_id` is not null))",
"cost_info": {
"read_cost": "4.51",
"eval_cost": "0.30",
"prefix_cost": "4.81",
"data_read_per_join": "96"
},
"used_columns": [
"filter_id",
"feature_id"
],
"attached_condition": "(`portal`.`q_product_filters`.`filter_id` in (1,3,11))"
}
},
{
"table": {
"table_name": "q_product_features",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"feature_id"
],
"key_length": "3",
"ref": [
"portal.q_product_filters.feature_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 0,
"filtered": "30.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "0.09",
"prefix_cost": "5.11",
"data_read_per_join": "194"
},
"used_columns": [
"feature_id",
"feature_type"
],
"attached_condition": "(`portal`.`q_product_features`.`feature_type` in ('S','M','E'))"
}
},
{
"table": {
"table_name": "products_categories",
"access_type": "ref",
"possible_keys": [
"PRIMARY",
"pt"
],
"key": "PRIMARY",
"used_key_parts": [
"category_id"
],
"key_length": "3",
"ref": [
"const"
],
"rows_examined_per_scan": 953,
"rows_produced_per_join": 428,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "11.16",
"eval_cost": "85.77",
"prefix_cost": "102.04",
"data_read_per_join": "6K"
},
"used_columns": [
"product_id",
"category_id"
]
}
},
{
"table": {
"table_name": "products",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"status"
],
"key": "PRIMARY",
"used_key_parts": [
"product_id"
],
"key_length": "3",
"ref": [
"portal.products_categories.product_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 399,
"filtered": "93.11",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "79.86",
"prefix_cost": "187.81",
"data_read_per_join": "2M"
},
"used_columns": [
"product_id",
"status",
"company_id",
"amount",
"usergroup_ids",
"tracking"
],
"attached_condition": "((case `portal`.`products`.`tracking` when 'O' then <cache>((NULL > 0)) when 'B' then (`portal`.`products`.`amount` > 0) else 1 end) and ((`portal`.`products`.`usergroup_ids` = '') or find_in_set(0,`portal`.`products`.`usergroup_ids`) or find_in_set(1,`portal`.`products`.`usergroup_ids`)) and (`portal`.`products`.`status` = 'A'))"
}
},
{
"table": {
"table_name": "companies",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"company_id"
],
"key_length": "4",
"ref": [
"portal.products.company_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 39,
"filtered": "10.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "7.99",
"prefix_cost": "267.67",
"data_read_per_join": "220K"
},
"used_columns": [
"company_id",
"status"
],
"attached_condition": "(`portal`.`companies`.`status` = 'A')"
}
},
{
"table": {
"table_name": "q_product_features_values",
"access_type": "ref",
"possible_keys": [
"PRIMARY",
"fl",
"variant_id",
"lang_code",
"product_id",
"fpl"
],
"key": "fpl",
"used_key_parts": [
"feature_id",
"product_id",
"lang_code"
],
"key_length": "12",
"ref": [
"portal.q_product_filters.feature_id",
"portal.products_categories.product_id",
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 39,
"filtered": "100.00",
"cost_info": {
"read_cost": "39.93",
"eval_cost": "7.99",
"prefix_cost": "315.59",
"data_read_per_join": "30K"
},
"used_columns": [
"feature_id",
"product_id",
"variant_id",
"lang_code"
]
}
},
{
"table": {
"table_name": "var_val_2",
"access_type": "ref",
"possible_keys": [
"PRIMARY",
"fl",
"lang_code",
"product_id",
"fpl"
],
"key": "fpl",
"used_key_parts": [
"feature_id",
"product_id",
"lang_code"
],
"key_length": "12",
"ref": [
"const",
"portal.products_categories.product_id",
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 39,
"filtered": "100.00",
"cost_info": {
"read_cost": "39.93",
"eval_cost": "7.99",
"prefix_cost": "363.50",
"data_read_per_join": "30K"
},
"used_columns": [
"feature_id",
"product_id",
"variant_id",
"lang_code"
],
"attached_condition": "<if>(found_match(var_val_2), ((`portal`.`var_val_2`.`variant_id` = 8226) or (`portal`.`q_product_filters`.`feature_id` = 2)), true)"
}
}
]
}
}
}
Result
filter_id |
variant_id |
1 |
7 |
1 |
8 |
1 |
9 |
1 |
10 |
1 |
13 |
1 |
14 |
1 |
15 |
1 |
16 |
1 |
27 |
1 |
28 |
1 |
66 |
1 |
67 |
1 |
69 |
1 |
70 |
1 |
71 |
1 |
72 |
1 |
73 |
1 |
75 |
1 |
76 |
1 |
78 |
1 |
79 |
1 |
80 |
1 |
81 |
1 |
297 |
1 |
5779 |
1 |
5781 |
1 |
5785 |
1 |
5839 |
1 |
5841 |
1 |
5842 |
1 |
5843 |
1 |
5844 |
1 |
5918 |
1 |
5957 |
1 |
6026 |
1 |
6087 |
1 |
6097 |
1 |
6098 |
1 |
6158 |
1 |
6180 |
1 |
6437 |
1 |
6643 |
1 |
6714 |
1 |
6728 |
1 |
7557 |
1 |
7559 |
1 |
7603 |
1 |
7651 |
1 |
8159 |
1 |
8163 |
1 |
8189 |
1 |
8190 |
1 |
8191 |
1 |
8192 |
1 |
8193 |
1 |
8197 |
1 |
8200 |
1 |
8203 |
1 |
8204 |
1 |
8206 |
1 |
8208 |
1 |
8210 |
1 |
8211 |
1 |
8212 |
1 |
8215 |
1 |
8219 |
1 |
8222 |
1 |
8224 |
1 |
8225 |
1 |
8226 |
1 |
8228 |
1 |
8230 |
1 |
8231 |
1 |
8235 |
1 |
8236 |
1 |
8237 |
1 |
8241 |
1 |
8246 |
1 |
8248 |
1 |
8253 |
1 |
8254 |
1 |
8284 |
1 |
8286 |
1 |
8288 |
1 |
8291 |
1 |
8293 |
1 |
8295 |
1 |
8296 |
1 |
8297 |
1 |
8298 |
1 |
8299 |
1 |
8301 |
1 |
8302 |
1 |
8304 |
1 |
8305 |
1 |
8306 |
1 |
8307 |
1 |
8346 |
1 |
8347 |
1 |
8348 |
1 |
8352 |
1 |
8353 |
1 |
8354 |
1 |
8358 |
1 |
8359 |
1 |
8362 |
1 |
8363 |
1 |
8365 |
1 |
8367 |
1 |
8368 |
1 |
8369 |
1 |
8370 |
1 |
8371 |
1 |
8380 |
1 |
8383 |
1 |
8384 |
1 |
8385 |
1 |
8386 |
1 |
8389 |
1 |
8390 |
1 |
8391 |
1 |
8393 |
1 |
8395 |
1 |
8396 |
1 |
8399 |
1 |
8405 |
1 |
8406 |
1 |
8407 |
1 |
8411 |
1 |
8412 |
1 |
8413 |
1 |
8414 |
1 |
8415 |
1 |
8416 |
1 |
8465 |