Skip to content

Instantly share code, notes, and snippets.

@spetrunia

spetrunia/q24b-10.11-print-loops.txt

Created November 21, 2022 17:48
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save spetrunia/250c48d49dfb9f91ff3fb27102adb929 to your computer and use it in GitHub Desktop.
Save spetrunia/250c48d49dfb9f91ff3fb27102adb929 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
q24b-10.11-print-loops.txt
{
"query_optimization": {
"r_total_time_ms": 14.02244903
},
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 193.6059154,
"nested_loop": [
{
"table": {
"table_name": "it",
"access_type": "ALL",
"possible_keys": ["PRIMARY"],
"loops": 1,
"r_loops": 1,
"rows": 113,
"r_rows": 113,
"r_table_time_ms": 0.022671627,
"r_other_time_ms": 0.006628152,
"filtered": 0.884955764,
"r_filtered": 0.884955752,
"attached_condition": "it.info = 'release dates'"
}
},
{
"block-nl-join": {
"table": {
"table_name": "rt",
"access_type": "ALL",
"possible_keys": ["PRIMARY"],
"loops": 1,
"r_loops": 1,
"rows": 12,
"r_rows": 12,
"r_table_time_ms": 0.003647458,
"r_other_time_ms": 0.002395677,
"filtered": 8.333333015,
"r_filtered": 8.333333333,
"attached_condition": "rt.`role` = 'actress'"
},
"buffer_type": "flat",
"buffer_size": "1Kb",
"join_type": "BNL",
"r_filtered": 100
}
},
{
"block-nl-join": {
"table": {
"table_name": "k",
"access_type": "ALL",
"possible_keys": ["PRIMARY"],
"loops": 1,
"r_loops": 1,
"rows": 134170,
"r_rows": 134170,
"r_table_time_ms": 24.78339076,
"r_other_time_ms": 5.679202322,
"filtered": 0.002981292,
"r_filtered": 0.002235969,
"attached_condition": "k.keyword in ('hero','martial-arts','hand-to-hand-combat','computer-animated-movie')"
},
"buffer_type": "incremental",
"buffer_size": "1Kb",
"join_type": "BNL",
"r_filtered": 100
}
},
{
"table": {
"table_name": "mk",
"access_type": "ref",
"possible_keys": ["keyword_id_movie_keyword", "movie_id_movie_keyword"],
"key": "keyword_id_movie_keyword",
"key_length": "4",
"used_key_parts": ["keyword_id"],
"ref": ["imdbload.k.id"],
"loops": 4,
"r_loops": 3,
"rows": 33,
"r_rows": 3232,
"r_table_time_ms": 82.16921498,
"r_other_time_ms": 1.63290548,
"filtered": 100,
"r_filtered": 100
}
},
{
"table": {
"table_name": "t",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["id"],
"ref": ["imdbload.mk.movie_id"],
"loops": 134.8716,
"r_loops": 9696,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 71.63400412,
"r_other_time_ms": 1.642582726,
"filtered": 6.127187e-6,
"r_filtered": 0.072194719,
"attached_condition": "t.production_year > 2010 and t.title like 'Kung Fu Panda%'"
}
},
{
"table": {
"table_name": "mc",
"access_type": "ref",
"possible_keys": [
"company_id_movie_companies",
"movie_id_movie_companies"
],
"key": "movie_id_movie_companies",
"key_length": "4",
"used_key_parts": ["movie_id"],
"ref": ["imdbload.mk.movie_id"],
"loops": 8.263835e-6,
"r_loops": 7,
"rows": 2,
"r_rows": 8.857142857,
"r_table_time_ms": 0.200426105,
"r_other_time_ms": 0.00877136,
"filtered": 100,
"r_filtered": 100
}
},
{
"table": {
"table_name": "cn",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["id"],
"ref": ["imdbload.mc.company_id"],
"loops": 1.983155e-5,
"r_loops": 62,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.124800443,
"r_other_time_ms": 0.008271983,
"filtered": 1.598509e-4,
"r_filtered": 9.677419355,
"attached_condition": "cn.country_code = '[us]' and cn.`name` = 'DreamWorks Animation'"
}
},
{
"table": {
"table_name": "mi",
"access_type": "ref",
"possible_keys": ["info_type_id_movie_info", "movie_id_movie_info"],
"key": "movie_id_movie_info",
"key_length": "4",
"used_key_parts": ["movie_id"],
"ref": ["imdbload.mk.movie_id"],
"loops": 3.17009e-11,
"r_loops": 6,
"rows": 6,
"r_rows": 90.66666667,
"r_table_time_ms": 1.955829776,
"r_other_time_ms": 0.053575198,
"filtered": 100,
"r_filtered": 1.654411765,
"attached_condition": "mi.info_type_id = it.`id` and mi.info is not null and (mi.info like 'Japan:%201%' or mi.info like 'USA:%201%')"
}
},
{
"table": {
"table_name": "ci",
"access_type": "ref",
"possible_keys": [
"movie_id_cast_info",
"person_id_cast_info",
"person_role_id_cast_info",
"role_id_cast_info"
],
"key": "movie_id_cast_info",
"key_length": "4",
"used_key_parts": ["movie_id"],
"ref": ["imdbload.mk.movie_id"],
"loops": 1.90497e-10,
"r_loops": 9,
"rows": 15,
"r_rows": 118.3333333,
"r_table_time_ms": 2.445883153,
"r_other_time_ms": 0.055790143,
"filtered": 2.226900816,
"r_filtered": 5.727699531,
"attached_condition": "ci.role_id = rt.`id` and ci.note in ('(voice)','(voice: Japanese version)','(voice) (uncredited)','(voice: English version)') and ci.person_role_id is not null"
}
},
{
"table": {
"table_name": "chn",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["id"],
"ref": ["imdbload.ci.person_role_id"],
"loops": 6.59439e-11,
"r_loops": 61,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.542997958,
"r_other_time_ms": 0.014612631,
"filtered": 100,
"r_filtered": 100
}
},
{
"table": {
"table_name": "n",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "4",
"used_key_parts": ["id"],
"ref": ["imdbload.ci.person_id"],
"loops": 6.59439e-11,
"r_loops": 61,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.450899512,
"r_other_time_ms": 0.018248411,
"filtered": 23.07119179,
"r_filtered": 27.86885246,
"attached_condition": "n.gender = 'f' and n.`name` like '%An%'"
}
},
{
"table": {
"table_name": "an",
"access_type": "ref",
"possible_keys": ["person_id_aka_name"],
"key": "person_id_aka_name",
"key_length": "4",
"used_key_parts": ["person_id"],
"ref": ["imdbload.ci.person_id"],
"loops": 1.5214e-11,
"r_loops": 17,
"rows": 1,
"r_rows": 3.352941176,
"r_table_time_ms": 0.12872039,
"r_other_time_ms": 0.015086428,
"filtered": 100,
"r_filtered": 100,
"using_index": true
}
}
]
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment