Query time 0.04353
JSON explain
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "377.40"
},
"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.10",
"prefix_cost": "0.00",
"data_read_per_join": "880"
},
"used_columns": [
"product_id",
"amount"
]
}
},
{
"table": {
"table_name": "cscart_categories",
"access_type": "range",
"possible_keys": [
"PRIMARY",
"c_status",
"p_category_id"
],
"key": "PRIMARY",
"used_key_parts": [
"category_id"
],
"key_length": "3",
"rows_examined_per_scan": 425,
"rows_produced_per_join": 8,
"filtered": "2.00",
"cost_info": {
"read_cost": "325.96",
"eval_cost": "0.85",
"prefix_cost": "326.81",
"data_read_per_join": "22K"
},
"used_columns": [
"category_id",
"company_id",
"usergroup_ids",
"status"
],
"attached_condition": "((`maniamall_cscart`.`cscart_categories`.`company_id` = 1) and (`maniamall_cscart`.`cscart_categories`.`category_id` in (1098,10851,10855,10857,10874,10886,32186,10887,10892,10893,10894,10895,10913,10914,10930,10933,10934,10940,10955,10956,10960,10964,10967,10976,10981,10983,10988,11013,11014,11085,11102,11112,11116,11122,1152,1165,12994,12995,13435,13446,13450,13461,1412,1452,1465,15302,1571,16568,16582,16836,16864,16887,17028,17042,17053,1720,19350,19351,19543,19564,19565,19580,19581,20749,20750,20751,20752,20753,20754,20755,20756,20757,20768,20758,20759,20760,20761,20762,20789,20763,20764,20765,20766,20767,20769,20770,20780,20781,20771,20772,21183,20773,20774,20782,21068,20775,20776,20777,20778,20779,20783,20784,20785,20786,20787,20788,20790,20791,21184,20979,20980,21033,21037,21112,21127,21128,19386,19387,19571,19601,20741,20742,20810,21164,21169,21238,21366,19474,21177,19506,19507,21587,19544,19545,19546,19547,20944,20945,20950,20946,20947,20948,20949,20951,20952,20953,20800,19555,19556,20815,20817,20819,20823,20825,20828,20829,20831,20926,20931,19731,19732,21139,21180,21333,21388,20976,20977,21041,21111,21116,21162,21163,21334,21179,20993,20994,21390,21065,21066,21159,21324,21325,21326,21327,21328,21329,21330,21331,21332,21335,21336,21337,21168,21172,21173,21175,21178,23102,23191,23192,23193,23194,23432,23197,23345,23878,23347,28716,23246,23281,23310,23311,23360,23816,23854,23884,23295,23296,23298,28607,28714,23301,23308,28636,23312,23861,23440,23512,23513,23729,23739,23740,23767,23784,23786,23787,2726,2876,30008,32185,3126,3129,3154,3172,3174,3177,3184,3251,3252,3256,3274,3275,3341,3358,3398,37427,37428,37430,37434,37436,37438,37440,37441,37442,37443,37444,37445,37446,37447,37449,37451,37452,37453,37455,37456,37457,37458,37459,37460,37461,37462,37463,37464,37465,37466,37467,37468,37469,37470,37471,37472,37473,37474,37475,37476,37477,37478,37482,37483,37484,37485,37486,37487,37488,37489,37490,37491,37492,37494,37495,37497,37500,37501,37502,37503,37505,37506,37509,37510,37511,37514,37515,37519,37520,37521,37522,37523,37524,37526,37527,37528,37529,37530,37531,37532,37533,37534,37535,37536,37537,37538,37539,37540,37541,37542,37543,37544,37545,37546,37547,37549,37550,37551,37553,37554,37555,37557,37558,37559,37560,37561,37562,37563,37564,37565,37566,37568,37571,37572,37573,37574,37575,37576,37577,37579,37580,37581,37582,37584,37585,37586,37587,37588,37589,37591,37592,37593,37604,37605,37606,37607,37608,37609,37610,37611,37612,37613,37614,5074,5789,5795,5837,5842,5853,5862,5865,5869,5870,5884,5971,6038,6381,7126,7127,7128,7131,7221,7345,7888,7890,8083,8094,8124,8132,8900)) and ((`maniamall_cscart`.`cscart_categories`.`usergroup_ids` = '') or (0 <> find_in_set(0,`maniamall_cscart`.`cscart_categories`.`usergroup_ids`)) or (0 <> find_in_set(1,`maniamall_cscart`.`cscart_categories`.`usergroup_ids`))) and (`maniamall_cscart`.`cscart_categories`.`status` in ('A','H')))"
}
},
{
"table": {
"table_name": "products_categories",
"access_type": "ref",
"possible_keys": [
"PRIMARY",
"pt"
],
"key": "PRIMARY",
"used_key_parts": [
"category_id"
],
"key_length": "3",
"ref": [
"maniamall_cscart.cscart_categories.category_id"
],
"rows_examined_per_scan": 19,
"rows_produced_per_join": 165,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "6.86",
"eval_cost": "16.56",
"prefix_cost": "350.23",
"data_read_per_join": "2K"
},
"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": [
"maniamall_cscart.products_categories.product_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 8,
"filtered": "5.00",
"cost_info": {
"read_cost": "0.00",
"eval_cost": "0.83",
"prefix_cost": "366.79",
"data_read_per_join": "36K"
},
"used_columns": [
"product_id",
"status",
"amount",
"usergroup_ids",
"tracking"
],
"attached_condition": "((`maniamall_cscart`.`products`.`status` = 'A') and (0 <> (case `maniamall_cscart`.`products`.`tracking` when 'O' then <cache>((NULL > 0)) when 'B' then (`maniamall_cscart`.`products`.`amount` > 0) else 1 end)) and ((`maniamall_cscart`.`products`.`usergroup_ids` = '') or (0 <> find_in_set(0,`maniamall_cscart`.`products`.`usergroup_ids`)) or (0 <> find_in_set(1,`maniamall_cscart`.`products`.`usergroup_ids`))))"
}
},
{
"table": {
"table_name": "popularity",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"total"
],
"key": "PRIMARY",
"used_key_parts": [
"product_id"
],
"key_length": "3",
"ref": [
"maniamall_cscart.products_categories.product_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 8,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "0.00",
"eval_cost": "0.83",
"prefix_cost": "367.62",
"data_read_per_join": "264"
},
"used_columns": [
"product_id"
]
}
},
{
"table": {
"table_name": "cscart_product_prices",
"access_type": "ref",
"possible_keys": [
"usergroup",
"product_id",
"lower_limit",
"usergroup_id"
],
"key": "usergroup",
"used_key_parts": [
"product_id"
],
"key_length": "3",
"ref": [
"maniamall_cscart.products_categories.product_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 2,
"filtered": "25.00",
"cost_info": {
"read_cost": "6.97",
"eval_cost": "0.21",
"prefix_cost": "375.42",
"data_read_per_join": "49"
},
"used_columns": [
"product_id",
"price",
"lower_limit",
"usergroup_id"
],
"attached_condition": "((`maniamall_cscart`.`cscart_product_prices`.`lower_limit` = 1) and (`maniamall_cscart`.`cscart_product_prices`.`usergroup_id` in (0,0,1)))"
}
},
{
"table": {
"table_name": "prices_2",
"access_type": "ref",
"possible_keys": [
"usergroup",
"product_id",
"lower_limit",
"usergroup_id"
],
"key": "usergroup",
"used_key_parts": [
"product_id"
],
"key_length": "3",
"ref": [
"maniamall_cscart.products_categories.product_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 0,
"filtered": "5.00",
"not_exists": true,
"cost_info": {
"read_cost": "1.74",
"eval_cost": "0.01",
"prefix_cost": "377.37",
"data_read_per_join": "2"
},
"used_columns": [
"product_id",
"price",
"lower_limit",
"usergroup_id"
],
"attached_condition": "(<if>(found_match(prices_2), (`maniamall_cscart`.`prices_2`.`price` is null), true) and <if>(is_not_null_compl(prices_2), ((`maniamall_cscart`.`prices_2`.`lower_limit` = 1) and (`maniamall_cscart`.`cscart_product_prices`.`price` > `maniamall_cscart`.`prices_2`.`price`) and (`maniamall_cscart`.`prices_2`.`usergroup_id` in (0,0,1))), true))"
}
},
{
"table": {
"table_name": "shared_prices",
"access_type": "ref",
"possible_keys": [
"usergroup",
"product_id",
"company_id",
"lower_limit",
"usergroup_id"
],
"key": "usergroup",
"used_key_parts": [
"product_id"
],
"key_length": "3",
"ref": [
"maniamall_cscart.products_categories.product_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 0,
"filtered": "100.00",
"index_condition": "<if>(is_not_null_compl(shared_prices), ((`maniamall_cscart`.`shared_prices`.`company_id` = 1) and (`maniamall_cscart`.`shared_prices`.`lower_limit` = 1) and (`maniamall_cscart`.`shared_prices`.`usergroup_id` in (0,0,1))), true)",
"cost_info": {
"read_cost": "0.03",
"eval_cost": "0.01",
"prefix_cost": "377.40",
"data_read_per_join": "3"
},
"used_columns": [
"product_id",
"price",
"lower_limit",
"company_id",
"usergroup_id"
]
}
}
]
}
}
Result
| min |
max |
| 3.74 |
395.31 |