Query time 0.01612
JSON explain
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "287.36"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": true,
"cost_info": {
"sort_cost": "28.28"
},
"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": 675,
"rows_produced_per_join": 303,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "8.03",
"eval_cost": "60.75",
"prefix_cost": "73.89",
"data_read_per_join": "4K"
},
"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": 282,
"filtered": "93.11",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "56.56",
"prefix_cost": "134.64",
"data_read_per_join": "1M"
},
"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": 28,
"filtered": "10.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "5.66",
"prefix_cost": "191.21",
"data_read_per_join": "156K"
},
"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": 28,
"filtered": "100.00",
"cost_info": {
"read_cost": "28.28",
"eval_cost": "5.66",
"prefix_cost": "225.14",
"data_read_per_join": "21K"
},
"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": 28,
"filtered": "100.00",
"cost_info": {
"read_cost": "28.28",
"eval_cost": "5.66",
"prefix_cost": "259.08",
"data_read_per_join": "21K"
},
"used_columns": [
"feature_id",
"product_id",
"variant_id",
"lang_code"
],
"attached_condition": "<if>(found_match(var_val_2), ((`portal`.`var_val_2`.`variant_id` = 8188) or (`portal`.`q_product_filters`.`feature_id` = 2)), true)"
}
}
]
}
}
}
Result
filter_id |
variant_id |
1 |
7 |
1 |
8 |
1 |
10 |
1 |
16 |
1 |
27 |
1 |
28 |
1 |
66 |
1 |
69 |
1 |
70 |
1 |
71 |
1 |
72 |
1 |
73 |
1 |
80 |
1 |
81 |
1 |
297 |
1 |
5779 |
1 |
5785 |
1 |
5839 |
1 |
5841 |
1 |
5842 |
1 |
5862 |
1 |
5943 |
1 |
5957 |
1 |
5958 |
1 |
5995 |
1 |
6026 |
1 |
6027 |
1 |
6028 |
1 |
6098 |
1 |
6155 |
1 |
6643 |
1 |
6672 |
1 |
6714 |
1 |
6731 |
1 |
6735 |
1 |
7558 |
1 |
7559 |
1 |
7562 |
1 |
7576 |
1 |
7603 |
1 |
8163 |
1 |
8167 |
1 |
8171 |
1 |
8188 |
1 |
8190 |
1 |
8191 |
1 |
8193 |
1 |
8194 |
1 |
8195 |
1 |
8196 |
1 |
8197 |
1 |
8198 |
1 |
8199 |
1 |
8200 |
1 |
8201 |
1 |
8202 |
1 |
8203 |
1 |
8204 |
1 |
8205 |
1 |
8207 |
1 |
8209 |
1 |
8210 |
1 |
8211 |
1 |
8212 |
1 |
8213 |
1 |
8214 |
1 |
8215 |
1 |
8216 |
1 |
8217 |
1 |
8218 |
1 |
8220 |
1 |
8221 |
1 |
8222 |
1 |
8223 |
1 |
8225 |
1 |
8226 |
1 |
8227 |
1 |
8229 |
1 |
8230 |
1 |
8231 |
1 |
8232 |
1 |
8233 |
1 |
8234 |
1 |
8235 |
1 |
8238 |
1 |
8239 |
1 |
8253 |
1 |
8254 |
1 |
8287 |
1 |
8289 |
1 |
8291 |
1 |
8292 |
1 |
8294 |
1 |
8298 |
1 |
8300 |
1 |
8347 |
1 |
8350 |
1 |
8351 |
1 |
8353 |
1 |
8354 |
1 |
8355 |
1 |
8357 |
1 |
8358 |
1 |
8361 |
1 |
8366 |
1 |
8370 |
1 |
8374 |
1 |
8377 |
1 |
8378 |
1 |
8379 |
1 |
8382 |
1 |
8387 |
1 |
8388 |
1 |
8389 |
1 |
8390 |
1 |
8391 |
1 |
8394 |
1 |
8396 |
1 |
8397 |
1 |
8398 |
1 |
8399 |
1 |
8400 |
1 |
8403 |
1 |
8404 |
1 |
8407 |
1 |
8408 |
1 |
8409 |
1 |
8410 |
1 |
8414 |
1 |
8416 |
1 |
8419 |
1 |
8420 |