Query time 0.00128
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 (30281,30282,30283,30284,30285,30286,30287,30288,30289,30290,30291,30292,30293,30294,30295,30296,30297,30298,30299,30300,30301,30302,30303,30304,30305,30306,30307,30309,30310,30311,30312,30313,30314,30315,30316,30317,30318,30320,30321,30322,30323,30324,30325,30326,30327,30330,30331,30333,30334,30336,30337,30345,30348,30350,30351,30352,30353,30383,30405,30408,30659,30665,30696,30697)) 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 |
30281 |
|
B |
22771 |
30282 |
|
B |
22769 |
30283 |
|
B |
22767 |
30284 |
|
B |
22765 |
30285 |
|
B |
22763 |
30286 |
|
B |
22762 |
30287 |
|
B |
22344 |
30288 |
|
B |
22352 |
30289 |
|
B |
22355 |
30290 |
|
B |
22358 |
30291 |
|
B |
22360 |
30292 |
|
B |
22364 |
30293 |
|
B |
22366 |
30294 |
|
B |
22367 |
30295 |
|
B |
22370 |
30296 |
|
B |
22390 |
30297 |
|
B |
22391 |
30298 |
|
B |
22392 |
30299 |
|
B |
22393 |
30300 |
|
B |
22394 |
30301 |
|
B |
22760 |
30302 |
|
B |
22758 |
30303 |
|
B |
22757 |
30304 |
|
B |
22756 |
30305 |
|
B |
22754 |
30306 |
|
B |
22752 |
30307 |
|
B |
22398 |
30309 |
|
B |
22403 |
30310 |
|
B |
22405 |
30311 |
|
B |
22407 |
30312 |
|
B |
22409 |
30313 |
|
B |
22410 |
30314 |
|
B |
22413 |
30315 |
|
B |
22414 |
30316 |
|
B |
22416 |
30317 |
|
B |
22431 |
30318 |
|
B |
22433 |
30320 |
|
B |
22437 |
30321 |
|
B |
22441 |
30322 |
|
B |
22386 |
30323 |
|
B |
22383 |
30325 |
|
B |
22381 |
30326 |
|
B |
22379 |
30327 |
|
B |
22377 |
30330 |
|
B |
22341 |
30331 |
|
B |
22339 |
30333 |
|
B |
22334 |
30334 |
|
B |
22333 |
30336 |
|
B |
22332 |
30337 |
|
B |
22331 |
30345 |
|
B |
22326 |
30348 |
|
B |
22324 |
30350 |
|
B |
22322 |
30351 |
|
B |
22321 |
30352 |
|
B |
22320 |
30353 |
|
B |
22319 |
30383 |
|
B |
22933 |
30405 |
|
B |
23052 |
30408 |
|
B |
23047 |
30659 |
|
B |
22146 |
30665 |
|
B |
22129 |
30696 |
|
B |
23115 |
30697 |
|
B |
23113 |