Query time 0.00081
JSON explain
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "460.81"
},
"grouping_operation": {
"using_filesort": false,
"nested_loop": [
{
"table": {
"table_name": "q_discussion",
"access_type": "range",
"possible_keys": [
"object_id"
],
"key": "object_id",
"used_key_parts": [
"object_id",
"object_type"
],
"key_length": "6",
"rows_examined_per_scan": 64,
"rows_produced_per_join": 64,
"filtered": "100.00",
"index_condition": "((`portal`.`q_discussion`.`object_id` in (6415,6416,6417,6418,6419,6420,6421,6422,6423,6424,6425,6426,6427,6428,6429,6430,6431,6432,6433,6434,6435,6436,6437,6438,6439,6440,6441,6442,6443,6444,6445,6446,6447,6448,6449,6450,6451,6452,6453,6454,6456,6457,6458,6459,6460,6461,6462,6463,6464,6465,6466,6467,6468,6469,6470,6471,6472,6473,6474,6475,6476,6477,6478,6479)) and (`portal`.`q_discussion`.`object_type` = 'P'))",
"cost_info": {
"read_cost": "140.81",
"eval_cost": "12.80",
"prefix_cost": "153.61",
"data_read_per_join": "1024"
},
"used_columns": [
"thread_id",
"object_id",
"object_type",
"type"
]
}
},
{
"table": {
"table_name": "q_discussion_posts",
"access_type": "ref",
"possible_keys": [
"thread_id",
"thread_id_2"
],
"key": "thread_id",
"used_key_parts": [
"thread_id"
],
"key_length": "3",
"ref": [
"portal.q_discussion.thread_id"
],
"rows_examined_per_scan": 2,
"rows_produced_per_join": 128,
"filtered": "100.00",
"cost_info": {
"read_cost": "128.00",
"eval_cost": "25.60",
"prefix_cost": "307.21",
"data_read_per_join": "56K"
},
"used_columns": [
"post_id",
"thread_id",
"status"
],
"attached_condition": "<if>(is_not_null_compl(q_discussion_posts), (`portal`.`q_discussion_posts`.`status` = 'A'), true)"
}
},
{
"table": {
"table_name": "q_discussion_rating",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"thread_id"
],
"key": "PRIMARY",
"used_key_parts": [
"post_id"
],
"key_length": "3",
"ref": [
"portal.q_discussion_posts.post_id"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 128,
"filtered": "100.00",
"cost_info": {
"read_cost": "128.00",
"eval_cost": "25.60",
"prefix_cost": "460.81",
"data_read_per_join": "2K"
},
"used_columns": [
"rating_value",
"post_id",
"thread_id"
],
"attached_condition": "<if>(is_not_null_compl(q_discussion_rating), ((`portal`.`q_discussion_rating`.`thread_id` = `portal`.`q_discussion`.`thread_id`) and (`portal`.`q_discussion_rating`.`rating_value` <> 0)), true)"
}
}
]
}
}
}
Result
product_id |
average_rating |
discussion_type |
discussion_thread_id |
6415 |
|
D |
2839 |
6416 |
|
D |
2840 |
6417 |
|
D |
2843 |
6418 |
|
D |
2845 |
6419 |
|
D |
2847 |
6420 |
|
D |
2848 |
6421 |
|
D |
2849 |
6422 |
|
D |
2850 |
6423 |
|
D |
2852 |
6424 |
|
D |
2854 |
6425 |
|
D |
2856 |
6426 |
|
D |
2857 |
6428 |
|
D |
2858 |
6429 |
|
D |
2859 |
6430 |
|
D |
2860 |
6431 |
|
D |
2861 |
6432 |
|
D |
2862 |
6433 |
|
D |
2863 |
6434 |
|
D |
2864 |
6435 |
|
D |
2865 |
6436 |
|
D |
2866 |
6437 |
|
D |
2867 |
6438 |
|
D |
2868 |
6439 |
|
D |
2869 |
6440 |
|
D |
2870 |
6441 |
|
D |
2872 |
6442 |
|
D |
2873 |
6443 |
|
D |
2874 |
6444 |
|
D |
2875 |
6445 |
|
D |
2876 |
6446 |
|
D |
2877 |
6447 |
|
D |
2878 |
6448 |
|
D |
2879 |
6449 |
|
D |
2880 |
6450 |
|
D |
2881 |
6451 |
|
D |
2882 |
6452 |
|
D |
2883 |
6453 |
|
D |
2884 |
6454 |
|
D |
2885 |
6456 |
|
D |
2887 |
6457 |
|
D |
2888 |
6458 |
|
D |
2889 |
6459 |
|
D |
2890 |
6460 |
|
D |
2891 |
6461 |
|
D |
2892 |
6462 |
|
D |
2893 |
6463 |
|
D |
2895 |
6464 |
|
D |
2896 |
6465 |
|
D |
2898 |
6466 |
|
D |
2899 |
6467 |
|
D |
2900 |
6468 |
|
D |
2901 |
6469 |
|
D |
2902 |
6470 |
|
D |
2903 |
6471 |
|
D |
2904 |
6472 |
|
D |
2905 |
6473 |
|
D |
2906 |
6474 |
|
D |
2907 |
6475 |
|
D |
2909 |
6476 |
|
D |
2910 |
6477 |
|
D |
2911 |
6478 |
|
D |
2912 |
6479 |
|
D |
2913 |