Query time 0.00256
JSON explain
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "46.85"
},
"ordering_operation": {
"using_filesort": true,
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": true,
"buffer_result": {
"using_temporary_table": true,
"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": "var_val_2",
"access_type": "ref",
"possible_keys": [
"PRIMARY",
"fl",
"variant_id",
"lang_code",
"product_id",
"fpl"
],
"key": "fl",
"used_key_parts": [
"feature_id",
"lang_code",
"variant_id"
],
"key_length": "12",
"ref": [
"const",
"const",
"const"
],
"rows_examined_per_scan": 27,
"rows_produced_per_join": 27,
"filtered": "100.00",
"cost_info": {
"read_cost": "27.00",
"eval_cost": "5.40",
"prefix_cost": "32.40",
"data_read_per_join": "20K"
},
"used_columns": [
"feature_id",
"product_id",
"variant_id",
"lang_code"
],
"attached_condition": "(`portal`.`var_val_2`.`product_id` is not null)"
}
},
{
"table": {
"table_name": "products",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"status"
],
"key": "PRIMARY",
"used_key_parts": [
"product_id"
],
"key_length": "3",
"ref": [
"portal.var_val_2.product_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 25,
"filtered": "93.11",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "5.03",
"prefix_cost": "37.80",
"data_read_per_join": "148K"
},
"used_columns": [
"product_id",
"product_type",
"status",
"company_id",
"amount",
"timestamp",
"usergroup_ids",
"tracking",
"parent_product_id"
],
"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": 2,
"filtered": "10.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "0.50",
"prefix_cost": "42.83",
"data_read_per_join": "13K"
},
"used_columns": [
"company_id",
"status",
"company"
],
"attached_condition": "(`portal`.`companies`.`status` = 'A')"
}
},
{
"table": {
"table_name": "products_categories",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"pt"
],
"key": "PRIMARY",
"used_key_parts": [
"category_id",
"product_id"
],
"key_length": "6",
"ref": [
"const",
"portal.var_val_2.product_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 2,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "0.00",
"eval_cost": "0.50",
"prefix_cost": "43.33",
"data_read_per_join": "40"
},
"used_columns": [
"product_id",
"category_id"
]
}
},
{
"table": {
"table_name": "descr1",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"product_id"
],
"key": "PRIMARY",
"used_key_parts": [
"product_id",
"lang_code"
],
"key_length": "9",
"ref": [
"portal.var_val_2.product_id",
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 2,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "0.50",
"prefix_cost": "43.83",
"data_read_per_join": "9K"
},
"used_columns": [
"product_id",
"lang_code",
"product"
]
}
},
{
"table": {
"table_name": "prices",
"access_type": "ref",
"possible_keys": [
"usergroup",
"product_id",
"lower_limit",
"usergroup_id"
],
"key": "usergroup",
"used_key_parts": [
"product_id"
],
"key_length": "3",
"ref": [
"portal.var_val_2.product_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 2,
"filtered": "99.95",
"using_index": true,
"cost_info": {
"read_cost": "2.51",
"eval_cost": "0.50",
"prefix_cost": "46.85",
"data_read_per_join": "60"
},
"used_columns": [
"product_id",
"lower_limit",
"usergroup_id"
],
"attached_condition": "((`portal`.`prices`.`lower_limit` = 1) and (`portal`.`prices`.`usergroup_id` in (0,0,1)))"
}
}
]
}
}
}
}
}
Result
product_id |
product |
company_name |
product_ids |
product_types |
parent_product_ids |
product_type |
parent_product_id |
8790 |
Panzer Glass Screen Protector for I Phone 8/7 Plus |
Shift Electronics LLC |
8790 |
P |
0 |
P |
0 |
8791 |
Panzer Glass Apple Watch Series 4 44mm |
Shift Electronics LLC |
8791 |
P |
0 |
P |
0 |
8792 |
Panzer Glass Standard Fit Privacy for iPhone XR |
Shift Electronics LLC |
8792 |
P |
0 |
P |
0 |
9050 |
PanzerGlass - Screen Protector For Apple iPhone 8/7 Clear Clear |
Shift Electronics LLC |
9050 |
P |
0 |
P |
0 |
9052 |
Panzerglass - Black Frame Case Friendly For iPhone XS/X |
Shift Electronics LLC |
9052 |
P |
0 |
P |
0 |
9053 |
Panzerglass - Standard Fit For iPhone XR |
Shift Electronics LLC |
9053 |
P |
0 |
P |
0 |
9054 |
PanzerGlass - Protective Case Cover For Apple iPhone XS Max Black |
Shift Electronics LLC |
9054 |
P |
0 |
P |
0 |
9055 |
Panzerglass - iPhone XR - Edge to Edge Screen Protector - Black Frame |
Shift Electronics LLC |
9055 |
P |
0 |
P |
0 |
9056 |
Panzerglass - Edge To Edge Black Frame For iPhone XS Max |
Shift Electronics LLC |
9056 |
P |
0 |
P |
0 |
9057 |
Panzerglass Privacy Screen Protector For iPhone 8/7 Plus |
Shift Electronics LLC |
9057 |
P |
0 |
P |
0 |
9058 |
Panzerglass - Edge To Edge Black For iPhone XR Privacy |
Shift Electronics LLC |
9058 |
P |
0 |
P |
0 |
9817 |
PanzerGlass Screen Protector Privacy For iPad Mini 4 |
Shift Electronics LLC |
9817 |
P |
0 |
P |
0 |
9818 |
PanzerGlass For iPad Pro 12.9 |
Shift Electronics LLC |
9818 |
P |
0 |
P |
0 |
9819 |
PanzerGlass Apple Watch Series 4 40mm |
Shift Electronics LLC |
9819 |
P |
0 |
P |
0 |
9820 |
PanzerGlass Apple Watch Series 4 44mm |
Shift Electronics LLC |
9820 |
P |
0 |
P |
0 |
9821 |
PanzerGlass Screen Protector for iPad Pro 10.5 Inch |
Shift Electronics LLC |
9821 |
P |
0 |
P |
0 |
9822 |
PanzerGlass Case Friendly - Jet Black / Black for iPhone 8/7/6S/6 |
Shift Electronics LLC |
9822 |
P |
0 |
P |
0 |
9823 |
PanzerGlass Case Friendly Jet Black / Black For iPhone 8/7/6S/6 Plus |
Shift Electronics LLC |
9823 |
P |
0 |
P |
0 |
9824 |
PanzerGlass Case Friendly White - For iPhone 8/7/6S/6 |
Shift Electronics LLC |
9824 |
P |
0 |
P |
0 |
9825 |
PanzerGlass Case Friendly White For iPhone 8/7/6S/6 Plus |
Shift Electronics LLC |
9825 |
P |
0 |
P |
0 |
9826 |
PanzerGlass Standard Fit For iPhone XS/X |
Shift Electronics LLC |
9826 |
P |
0 |
P |
0 |
9827 |
PanzerGlass Backglass Clear for iPhone XR |
Shift Electronics LLC |
9827 |
P |
0 |
P |
0 |
9828 |
PanzerGlass Back Glass Screen Protector For iPhone XS Max |
Shift Electronics LLC |
9828 |
P |
0 |
P |
0 |
9829 |
PanzerGlass Back Glass Screen Protector For iPhone XS/X |
Shift Electronics LLC |
9829 |
P |
0 |
P |
0 |
9830 |
PanzerGlass Screen Protector For Apple iPad Pro 11 |
Shift Electronics LLC |
9830 |
P |
0 |
P |
0 |
9831 |
PanzerGlass Screen Protector For Apple iPad Pro 12.9 |
Shift Electronics LLC |
9831 |
P |
0 |
P |
0 |
9832 |
PanzerGlass For Huawei Nova 3E |
Shift Electronics LLC |
9832 |
P |
0 |
P |
0 |
9833 |
PanzerGlass Huawei Mate 20 Black Curved Edges Case Friendly |
Shift Electronics LLC |
9833 |
P |
0 |
P |
0 |
9834 |
PanzerGlass Huawei Mate 20 Pro Black Curved Edges Case Friendly |
Shift Electronics LLC |
9834 |
P |
0 |
P |
0 |
9835 |
PanzerGlass Premium Clear For Samsung S8 |
Shift Electronics LLC |
9835 |
P |
0 |
P |
0 |