Query time 0.35330
JSON explain
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "168430.81"
},
"ordering_operation": {
"using_filesort": true,
"grouping_operation": {
"using_temporary_table": true,
"using_filesort": false,
"nested_loop": [
{
"table": {
"table_name": "cscart_products",
"access_type": "ref",
"possible_keys": [
"PRIMARY",
"status"
],
"key": "status",
"used_key_parts": [
"status"
],
"key_length": "3",
"ref": [
"const"
],
"rows_examined_per_scan": 160561,
"rows_produced_per_join": 16056,
"filtered": "10.00",
"cost_info": {
"read_cost": "17970.00",
"eval_cost": "3211.22",
"prefix_cost": "50082.20",
"data_read_per_join": "122M"
},
"used_columns": [
"product_id",
"status",
"company_id"
],
"attached_condition": "(`webmarco`.`cscart_products`.`company_id` = 707)"
}
},
{
"table": {
"table_name": "cscart_products_categories",
"access_type": "ref",
"possible_keys": [
"PRIMARY",
"link_type",
"pt"
],
"key": "pt",
"used_key_parts": [
"product_id"
],
"key_length": "3",
"ref": [
"webmarco.cscart_products.product_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 26643,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "16071.60",
"eval_cost": "5328.79",
"prefix_cost": "71482.59",
"data_read_per_join": "416K"
},
"used_columns": [
"product_id",
"category_id"
]
}
},
{
"table": {
"table_name": "cscart_categories",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"p_category_id"
],
"key": "PRIMARY",
"used_key_parts": [
"category_id"
],
"key_length": "3",
"ref": [
"webmarco.cscart_products_categories.category_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 26643,
"filtered": "100.00",
"cost_info": {
"read_cost": "26643.93",
"eval_cost": "5328.79",
"prefix_cost": "103455.31",
"data_read_per_join": "154M"
},
"used_columns": [
"category_id",
"position"
]
}
},
{
"table": {
"table_name": "cscart_product_variation_group_products",
"access_type": "ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"product_id"
],
"key_length": "3",
"ref": [
"webmarco.cscart_products.product_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 29219,
"filtered": "100.00",
"cost_info": {
"read_cost": "26643.93",
"eval_cost": "5843.82",
"prefix_cost": "135943.06",
"data_read_per_join": "456K"
},
"used_columns": [
"product_id",
"parent_product_id",
"group_id"
],
"attached_condition": "<if>(found_match(cscart_product_variation_group_products), ((`webmarco`.`cscart_product_variation_group_products`.`parent_product_id` = 0) or (not(<in_optimizer>(`webmarco`.`cscart_products`.`product_id`,<exists>(<index_lookup>(<cache>(`webmarco`.`cscart_products_categories`.`product_id`) in cscart_product_variation_group_products on PRIMARY)))))), true)",
"attached_subqueries": [
{
"dependent": true,
"cacheable": false,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "1.22"
},
"table": {
"table_name": "cscart_product_variation_group_products",
"access_type": "index_subquery",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"product_id"
],
"key_length": "3",
"ref": [
"func"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.22",
"prefix_cost": "1.22",
"data_read_per_join": "17"
},
"used_columns": [
"product_id"
]
}
}
}
]
}
},
{
"table": {
"table_name": "cscart_category_descriptions",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"category_id",
"lang_code"
],
"key_length": "9",
"ref": [
"webmarco.cscart_products_categories.category_id",
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 29219,
"filtered": "100.00",
"cost_info": {
"read_cost": "26643.93",
"eval_cost": "5843.82",
"prefix_cost": "168430.81",
"data_read_per_join": "116M"
},
"used_columns": [
"category_id",
"lang_code",
"category"
]
}
}
]
}
}
}
}
Result
count |
category |
category_id |
1 |
Storage & Organization |
3228 |
1 |
Cars |
10438 |
1 |
MP3 Player & Mobile Phone Accessory Sets |
9435 |
1 |
Waste Containment Accessories |
3231 |
1 |
Weight Lifting Belts |
10336 |
2 |
Water Dispensers |
7301 |
1 |
Lawn & Garden |
10300 |
1 |
Wood Stove Fans & Blowers |
3002 |
1 |
Moisture Absorbers |
3224 |
1 |
Air Purifiers |
9289 |
8 |
Electronics |
10269 |
1 |
Home & Garden |
10260 |
1 |
Measuring Scales |
8229 |
3 |
Clocks |
3087 |
1 |
Heating, Ventilation & Air Conditioning |
6809 |
1 |
Mobile Phones |
4575 |
1 |
Streaming & Home Media Players |
5014 |
1 |
Ventilation Fans |
9386 |
1 |
MP3 Player Accessories |
9434 |
2 |
Shoe Racks & Organizers |
3407 |
1 |
Activity Toys |
6975 |
1 |
Automotive Alarm Accessories |
9053 |
1 |
Circuit Prototyping |
4317 |
1 |
Nail Dryers |
4299 |
1 |
Camera Circuit Boards |
5039 |
1 |
Sports Fan Accessories |
5519 |
1 |
Food Dispensers |
6579 |
1 |
House Cleaning and Sanitation |
1516 |
1 |
Moving & Shipping Boxes |
6426 |
2 |
Cigarettes |
3631 |
2 |
Health Care |
4254 |
1 |
Baby Activity Toys |
7008 |
2 |
Cameras |
5821 |
2 |
Humidistats |
6897 |
1 |
Air Conditioner Filters |
3095 |
1 |
Baby Carrier Accessories |
7464 |
1 |
Canoe Accessories |
8440 |
1 |
Lighting Accessories |
5522 |
1 |
Games |
6448 |
2 |
Toy Cars |
7849 |
1 |
Headphones |
9662 |
1 |
Body Weight Scale Accessories |
5597 |
1 |
Sewing Machines |
8823 |
1 |
Bath Brushes |
4746 |
1 |
Fan Accessories |
3144 |
5 |
Home & Garden |
2910 |
1 |
Hair Dryers |
5256 |
1 |
Outdoor Storage Boxes |
5152 |
1 |
Hair Straighteners |
5270 |
2 |
Mobile Phone Accessories |
9720 |
1 |
Hair Clipper & Trimmer Accessories |
4418 |
1 |
Guitar Humidifiers |
7217 |
2 |
Washing Machines |
3060 |
1 |
MP3 Players |
5164 |
2 |
Humidifier Accessories |
3185 |
1 |
Humidifiers |
2985 |
1 |
Vacuums |
3048 |
1 |
Hair Styling Tool Accessories |
4903 |
7 |
Payments and Telephone Top-ups |
2601 |
1 |
Body Weight Scales |
5968 |
1 |
Puffed Rice Cakes |
3933 |
1 |
Accessories |
10041 |
1 |
Piggy Banks & Money Jars |
3487 |
1 |
Watches |
2851 |
1 |
Toys |
6928 |