Query time 0.01180
JSON explain
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "249.92"
},
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": true,
"cost_info": {
"sort_cost": "6.22"
},
"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_product_features",
"access_type": "const",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"feature_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": "432"
},
"used_columns": [
"feature_id",
"feature_type"
]
}
},
{
"table": {
"table_name": "q_product_filters",
"access_type": "ref",
"possible_keys": [
"PRIMARY",
"feature_id"
],
"key": "feature_id",
"used_key_parts": [
"feature_id"
],
"key_length": "3",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 0,
"filtered": "50.00",
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.10",
"prefix_cost": "1.20",
"data_read_per_join": "32"
},
"used_columns": [
"filter_id",
"feature_id"
],
"attached_condition": "(`portal`.`q_product_filters`.`filter_id` in (1,3))"
}
},
{
"table": {
"table_name": "products_categories",
"access_type": "range",
"possible_keys": [
"PRIMARY",
"pt"
],
"key": "PRIMARY",
"used_key_parts": [
"category_id"
],
"key_length": "3",
"rows_examined_per_scan": 668,
"rows_produced_per_join": 334,
"filtered": "100.00",
"using_index": true,
"using_join_buffer": "Block Nested Loop",
"cost_info": {
"read_cost": "75.64",
"eval_cost": "66.80",
"prefix_cost": "143.64",
"data_read_per_join": "5K"
},
"used_columns": [
"product_id",
"category_id"
],
"attached_condition": "(`portal`.`products_categories`.`category_id` in (249,250,435,436,490,496,500,506,251,492,497,501,507,252,498,502,508,253,499,503,509,254,489,504,255,505,532,256,271,433,434,459,484,533,535,579,595))"
}
},
{
"table": {
"table_name": "q_categories",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"c_status",
"p_category_id"
],
"key": "PRIMARY",
"used_key_parts": [
"category_id"
],
"key_length": "3",
"ref": [
"portal.products_categories.category_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 66,
"filtered": "20.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "13.36",
"prefix_cost": "210.44",
"data_read_per_join": "176K"
},
"used_columns": [
"category_id",
"usergroup_ids",
"status"
],
"attached_condition": "(((`portal`.`q_categories`.`usergroup_ids` = '') or find_in_set(0,`portal`.`q_categories`.`usergroup_ids`) or find_in_set(1,`portal`.`q_categories`.`usergroup_ids`)) and (`portal`.`q_categories`.`status` in ('A','H')))"
}
},
{
"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": 62,
"filtered": "93.11",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "12.44",
"prefix_cost": "223.80",
"data_read_per_join": "366K"
},
"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": 6,
"filtered": "10.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "1.24",
"prefix_cost": "236.24",
"data_read_per_join": "34K"
},
"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": [
"const",
"portal.products_categories.product_id",
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 6,
"filtered": "100.00",
"cost_info": {
"read_cost": "6.22",
"eval_cost": "1.24",
"prefix_cost": "243.71",
"data_read_per_join": "4K"
},
"used_columns": [
"feature_id",
"product_id",
"variant_id",
"lang_code"
]
}
}
]
}
}
}
Result
filter_id |
variant_id |
1 |
183 |
1 |
211 |
1 |
229 |
1 |
237 |
1 |
242 |
1 |
5862 |
1 |
5910 |
1 |
5913 |
1 |
5916 |
1 |
6010 |
1 |
6011 |
1 |
6153 |
1 |
6439 |
1 |
6533 |
1 |
6536 |
1 |
6539 |
1 |
6541 |
1 |
6542 |
1 |
6548 |
1 |
6550 |
1 |
6551 |
1 |
6553 |
1 |
6555 |
1 |
6556 |
1 |
6557 |
1 |
6559 |
1 |
6562 |
1 |
6564 |
1 |
6567 |
1 |
6568 |
1 |
6570 |
1 |
6572 |
1 |
6578 |
1 |
6580 |
1 |
6581 |
1 |
6582 |
1 |
6584 |
1 |
6587 |
1 |
6589 |
1 |
6590 |
1 |
6592 |
1 |
6594 |
1 |
6597 |
1 |
6600 |
1 |
6610 |
1 |
6613 |
1 |
6617 |
1 |
6618 |
1 |
6621 |
1 |
6622 |
1 |
6625 |
1 |
6627 |
1 |
6628 |
1 |
6629 |
1 |
6630 |
1 |
6634 |
1 |
6635 |
1 |
6636 |
1 |
6637 |
1 |
6638 |
1 |
6641 |
1 |
6642 |
1 |
6645 |
1 |
6646 |
1 |
6650 |
1 |
6651 |
1 |
6653 |
1 |
6654 |
1 |
6657 |
1 |
6659 |
1 |
6664 |
1 |
6666 |
1 |
6667 |
1 |
6669 |
1 |
6672 |
1 |
6674 |
1 |
6675 |
1 |
6676 |
1 |
6679 |
1 |
6682 |
1 |
6688 |
1 |
6690 |
1 |
6691 |
1 |
6693 |
1 |
6695 |
1 |
6718 |
1 |
6719 |
1 |
6720 |
1 |
6721 |
1 |
6724 |
1 |
6727 |
1 |
6729 |
1 |
6732 |
1 |
6734 |
1 |
6735 |
1 |
6736 |
1 |
6746 |
1 |
6748 |
1 |
6751 |
1 |
6753 |
1 |
6755 |
1 |
6757 |
1 |
6765 |
1 |
6827 |
1 |
6840 |
1 |
6861 |
1 |
6866 |
1 |
6867 |
1 |
6868 |
1 |
6872 |
1 |
6874 |
1 |
6876 |
1 |
6878 |
1 |
6879 |
1 |
6884 |
1 |
6889 |
1 |
6892 |
1 |
6896 |
1 |
6900 |
1 |
6901 |
1 |
6910 |
1 |
6915 |
1 |
6922 |
1 |
6924 |
1 |
6926 |
1 |
6928 |
1 |
6934 |
1 |
6936 |
1 |
6938 |
1 |
6941 |
1 |
6943 |
1 |
6945 |
1 |
6946 |
1 |
6947 |
1 |
6948 |
1 |
6949 |
1 |
6951 |
1 |
6953 |
1 |
6956 |
1 |
6958 |
1 |
6959 |
1 |
6960 |
1 |
6966 |
1 |
6996 |
1 |
7002 |
1 |
7005 |
1 |
7009 |
1 |
7010 |
1 |
7013 |
1 |
7014 |
1 |
7029 |
1 |
7034 |
1 |
7037 |
1 |
7040 |
1 |
7047 |
1 |
7051 |
1 |
7053 |
1 |
7063 |
1 |
7064 |
1 |
7065 |
1 |
7069 |
1 |
7075 |
1 |
7077 |
1 |
7105 |
1 |
7107 |
1 |
7111 |
1 |
7117 |
1 |
7118 |
1 |
7120 |
1 |
7123 |
1 |
7125 |
1 |
7129 |
1 |
7131 |
1 |
7133 |
1 |
7136 |
1 |
7140 |
1 |
7142 |
1 |
7148 |
1 |
7151 |
1 |
7152 |
1 |
7153 |
1 |
7154 |
1 |
7157 |
1 |
7158 |
1 |
7177 |
1 |
7181 |
1 |
7182 |
1 |
7187 |
1 |
7190 |
1 |
7191 |
1 |
7192 |
1 |
7193 |
1 |
7194 |
1 |
7197 |
1 |
7198 |
1 |
7199 |
1 |
7201 |
1 |
7202 |
1 |
7203 |
1 |
7204 |
1 |
7207 |
1 |
7208 |
1 |
7210 |
1 |
7226 |
1 |
7233 |
1 |
7234 |
1 |
7238 |
1 |
7242 |
1 |
7243 |
1 |
7244 |
1 |
7246 |
1 |
7249 |
1 |
7251 |
1 |
7253 |
1 |
7263 |
1 |
7264 |
1 |
7265 |
1 |
7266 |
1 |
7282 |
1 |
7284 |
1 |
7287 |
1 |
7288 |
1 |
7291 |
1 |
7295 |
1 |
7297 |
1 |
7300 |
1 |
7301 |
1 |
7302 |
1 |
7457 |
1 |
7613 |
1 |
7617 |
1 |
7652 |
1 |
7837 |
1 |
7850 |
1 |
8815 |
1 |
9263 |
1 |
9267 |
1 |
9268 |
1 |
9270 |
1 |
9290 |
1 |
9300 |
1 |
9621 |
1 |
9745 |