MariaDB [arche_prod]> set optimizer_trace=1;
Query OK, 0 rows affected (0,000 sec)
MariaDB [arche_prod]> set @@optimizer_adjust_secondary_key_costs='fix_card_multiplier,fix_reuse_range_for_ref';
Query OK, 0 rows affected (0,000 sec)
MariaDB [arche_prod]> ANALYZE FORMAT=JSON SELECT cc.id,cc.name,cc.idnumber,cc.parent,cc.sortorder,cc.coursecount,cc.visible,cc.depth,cc.path, ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.in
stanceid AS ctxinstance, ctx.locked AS ctxlocked FROM mdl_course_categories cc JOIN mdl_context ctx ON cc.id = ctx.instanceid AND ctx.contextlevel = '40' JOIN mdl_role_assignments ra ON ra.contextid = ctx.id WHERE ctx.path LIKE '/1/%' AND
ra.userid = '78244'
nstance, ctx.locked AS ctxlocked FROM mdl_course_categories cc JOIN mdl_context ctx ON cc.id = ctx.instanceid AND ctx.contextlevel = '40' JOIN mdl_role_capabilities rc ON rc.contextid = ctx.id JOIN mdl_role_assignments rc_ra ON rc_ra.rolei
d = rc.roleid JOIN mdl_context rc_ra_ctx ON rc_ra_ctx.id = rc_ra.contextid WHERE ctx.path LIKE '/1/%' AND rc_ra.userid = '78244' AND (ctx.path = rc_ra_ctx.path OR ctx.path LIKE CONCAT(rc_ra_ctx.path, '/%'));
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ANALYZE |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"query_optimization": {
"r_total_time_ms": 1.182400318
},
"query_block": {
"union_result": {
"table_name": "<union1,2>",
"access_type": "ALL",
"r_loops": 1,
"r_rows": 3,
"query_specifications": [
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.129972086,
"nested_loop": [
{
"table": {
"table_name": "ra",
"access_type": "ref",
"possible_keys": [
"mdl_roleassi_useconrol_ix",
"mdl_roleassi_con_ix",
"mdl_roleassi_use_ix"
],
"key": "mdl_roleassi_useconrol_ix",
"key_length": "8",
"used_key_parts": ["userid"],
"ref": ["const"],
"r_loops": 1,
"rows": 23,
"r_rows": 23,
"r_table_time_ms": 0.021667401,
"r_other_time_ms": 0.010154083,
"r_engine_stats": {
"pages_accessed": 3
},
"filtered": 100,
"r_filtered": 100,
"using_index": true
}
},
{
"table": {
"table_name": "ctx",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"mdl_cont_conins_uix",
"mdl_cont_ins_ix",
"mdl_cont_pat_ix"
],
"key": "PRIMARY",
"key_length": "8",
"used_key_parts": ["id"],
"ref": ["arche_prod.ra.contextid"],
"r_loops": 23,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.087926635,
"r_other_time_ms": 0.003228621,
"r_engine_stats": {
"pages_accessed": 66
},
"filtered": 0.135051519,
"r_filtered": 0,
"attached_condition": "ctx.contextlevel = 40 and ctx.`path` like '/1/%'"
}
},
{
"table": {
"table_name": "cc",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "8",
"used_key_parts": ["id"],
"ref": ["arche_prod.ctx.instanceid"],
"r_loops": 0,
"rows": 1,
"r_rows": null,
"r_engine_stats": {},
"filtered": 100,
"r_filtered": null
}
}
]
}
},
{
"query_block": {
"select_id": 2,
"operation": "UNION",
"r_loops": 1,
"r_total_time_ms": 672.5885696,
"nested_loop": [
{
"table": {
"table_name": "rc_ra",
"access_type": "ref",
"possible_keys": [
"mdl_roleassi_rolcon_ix",
"mdl_roleassi_useconrol_ix",
"mdl_roleassi_rol_ix",
"mdl_roleassi_con_ix",
"mdl_roleassi_use_ix"
],
"key": "mdl_roleassi_useconrol_ix",
"key_length": "8",
"used_key_parts": ["userid"],
"ref": ["const"],
"r_loops": 1,
"rows": 23,
"r_rows": 23,
"r_table_time_ms": 0.03438359,
"r_other_time_ms": 0.030318987,
"r_engine_stats": {
"pages_accessed": 3
},
"filtered": 100,
"r_filtered": 100,
"using_index": true
}
},
{
"table": {
"table_name": "rc_ra_ctx",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY", "mdl_cont_pat_ix"],
"key": "PRIMARY",
"key_length": "8",
"used_key_parts": ["id"],
"ref": ["arche_prod.rc_ra.contextid"],
"r_loops": 23,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.105490124,
"r_other_time_ms": 0.016569395,
"r_engine_stats": {
"pages_accessed": 66
},
"filtered": 100,
"r_filtered": 100
}
},
{
"table": {
"table_name": "rc",
"access_type": "ref",
"possible_keys": [
"mdl_rolecapa_rolconcap_uix",
"mdl_rolecapa_rol_ix",
"mdl_rolecapa_con_ix"
],
"key": "mdl_rolecapa_rolconcap_uix",
"key_length": "8",
"used_key_parts": ["roleid"],
"ref": ["arche_prod.rc_ra.roleid"],
"r_loops": 23,
"rows": 1017,
"r_rows": 12045.86957,
"r_table_time_ms": 67.99440653,
"r_other_time_ms": 24.67118133,
"r_engine_stats": {
"pages_accessed": 887
},
"filtered": 100,
"r_filtered": 100,
"using_index": true
}
},
{
"table": {
"table_name": "ctx",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"mdl_cont_conins_uix",
"mdl_cont_ins_ix",
"mdl_cont_pat_ix"
],
"key": "PRIMARY",
"key_length": "8",
"used_key_parts": ["id"],
"ref": ["arche_prod.rc.contextid"],
"r_loops": 277055,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 560.8064515,
"r_other_time_ms": 18.85439611,
"r_engine_stats": {
"pages_accessed": 785809
},
"filtered": 0.135051519,
"r_filtered": 0.001804696,
"attached_condition": "ctx.contextlevel = 40 and ctx.`path` like '/1/%' and (ctx.`path` = rc_ra_ctx.`path` or ctx.`path` like concat(rc_ra_ctx.`path`,'/%'))"
}
},
{
"table": {
"table_name": "cc",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "8",
"used_key_parts": ["id"],
"ref": ["arche_prod.ctx.instanceid"],
"r_loops": 5,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.013700674,
"r_other_time_ms": 0.059638639,
"r_engine_stats": {
"pages_accessed": 6
},
"filtered": 100,
"r_filtered": 100
}
}
]
}
}
]
}
}
} |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,672 sec)
MariaDB [arche_prod]> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
QUERY: ANALYZE FORMAT=JSON SELECT cc.id,cc.name,cc.idnumber,cc.parent,cc.sortorder,cc.coursecount,cc.visible,cc.depth,cc.path, ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, ctx.locked AS ctxlocked FROM mdl_course_categories cc JOIN mdl_context ctx ON cc.id = ctx.instanceid AND ctx.contextlevel = '40' JOIN mdl_role_assignments ra ON ra.contextid = ctx.id WHERE ctx.path LIKE '/1/%' AND ra.userid = '78244' UNION SELECT cc.id,cc.name,cc.idnumber,cc.parent,cc.sortorder,cc.coursecount,cc.visible,cc.depth,cc.path, ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, ctx.locked AS ctxlocked FROM mdl_course_categories cc JOIN mdl_context ctx ON cc.id = ctx.instanceid AND ctx.contextlevel = '40' JOIN mdl_role_capabilities rc ON rc.contextid = ctx.id JOIN mdl_role_assignments rc_ra ON rc_ra.roleid = rc.roleid JOIN mdl_context rc_ra_ctx ON rc_ra_ctx.id = rc_ra.contextid WHERE ctx.path LIKE '/1/%' AND rc_ra.userid = '78244' AND (ctx.path = rc_ra_ctx.path OR ctx.path LIKE CONCAT(rc_ra_ctx.path, '/%'))
TRACE: {
"steps": [
{
"join_preparation": {
"select_id": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select cc.`id` AS `id`,cc.`name` AS `name`,cc.idnumber AS idnumber,cc.parent AS parent,cc.sortorder AS sortorder,cc.coursecount AS coursecount,cc.`visible` AS `visible`,cc.depth AS depth,cc.`path` AS `path`,ctx.`id` AS ctxid,ctx.`path` AS ctxpath,ctx.depth AS ctxdepth,ctx.contextlevel AS ctxlevel,ctx.instanceid AS ctxinstance,ctx.`locked` AS ctxlocked from ((mdl_course_categories cc join mdl_context ctx on(cc.`id` = ctx.instanceid and ctx.contextlevel = 40)) join mdl_role_assignments ra on(ra.contextid = ctx.`id`)) where ctx.`path` like '/1/%' and ra.userid = 78244"
}
]
}
},
{
"join_preparation": {
"select_id": 2,
"steps": [
{
"expanded_query": "/* select#2 */ select cc.`id` AS `id`,cc.`name` AS `name`,cc.idnumber AS idnumber,cc.parent AS parent,cc.sortorder AS sortorder,cc.coursecount AS coursecount,cc.`visible` AS `visible`,cc.depth AS depth,cc.`path` AS `path`,ctx.`id` AS ctxid,ctx.`path` AS ctxpath,ctx.depth AS ctxdepth,ctx.contextlevel AS ctxlevel,ctx.instanceid AS ctxinstance,ctx.`locked` AS ctxlocked from ((((mdl_course_categories cc join mdl_context ctx on(cc.`id` = ctx.instanceid and ctx.contextlevel = 40)) join mdl_role_capabilities rc on(rc.contextid = ctx.`id`)) join mdl_role_assignments rc_ra on(rc_ra.roleid = rc.roleid)) join mdl_context rc_ra_ctx on(rc_ra_ctx.`id` = rc_ra.contextid)) where ctx.`path` like '/1/%' and rc_ra.userid = 78244 and (ctx.`path` = rc_ra_ctx.`path` or ctx.`path` like concat(rc_ra_ctx.`path`,'/%'))"
}
]
}
},
{
"join_optimization": {
"select_id": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "ctx.`path` like '/1/%' and ra.userid = 78244 and ra.contextid = ctx.`id` and cc.`id` = ctx.instanceid and ctx.contextlevel = 40",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "ctx.`path` like '/1/%' and multiple equal(78244, ra.userid) and multiple equal(ra.contextid, ctx.`id`) and multiple equal(cc.`id`, ctx.instanceid) and multiple equal(40, ctx.contextlevel)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "ctx.`path` like '/1/%' and multiple equal(78244, ra.userid) and multiple equal(ra.contextid, ctx.`id`) and multiple equal(cc.`id`, ctx.instanceid) and multiple equal(40, ctx.contextlevel)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "ctx.`path` like '/1/%' and multiple equal(78244, ra.userid) and multiple equal(ra.contextid, ctx.`id`) and multiple equal(cc.`id`, ctx.instanceid) and multiple equal(40, ctx.contextlevel)"
}
]
}
},
{
"table_dependencies": [
{
"table": "cc",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
},
{
"table": "ctx",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": []
},
{
"table": "ra",
"row_may_be_null": false,
"map_bit": 2,
"depends_on_map_bits": []
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "cc",
"index": "PRIMARY",
"field": "id",
"equals": "ctx.instanceid",
"null_rejecting": false
},
{
"table": "ctx",
"index": "PRIMARY",
"field": "id",
"equals": "ra.contextid",
"null_rejecting": false
},
{
"table": "ctx",
"index": "mdl_cont_conins_uix",
"field": "contextlevel",
"equals": "40",
"null_rejecting": false
},
{
"table": "ctx",
"index": "mdl_cont_conins_uix",
"field": "instanceid",
"equals": "cc.`id`",
"null_rejecting": false
},
{
"table": "ctx",
"index": "mdl_cont_ins_ix",
"field": "instanceid",
"equals": "cc.`id`",
"null_rejecting": false
},
{
"table": "ctx",
"index": "mdl_cont_ins_ix",
"field": "id",
"equals": "ra.contextid",
"null_rejecting": false
},
{
"table": "ra",
"index": "mdl_roleassi_useconrol_ix",
"field": "userid",
"equals": "78244",
"null_rejecting": false
},
{
"table": "ra",
"index": "mdl_roleassi_useconrol_ix",
"field": "contextid",
"equals": "ctx.`id`",
"null_rejecting": false
},
{
"table": "ra",
"index": "mdl_roleassi_con_ix",
"field": "contextid",
"equals": "ctx.`id`",
"null_rejecting": false
},
{
"table": "ra",
"index": "mdl_roleassi_use_ix",
"field": "userid",
"equals": "78244",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "cc",
"table_scan": {
"rows": 3737,
"cost": 97
}
},
{
"table": "ctx",
"range_analysis": {
"table_scan": {
"rows": 1328382,
"cost": 272253.4
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_cont_conins_uix",
"usable": true,
"key_parts": ["contextlevel", "instanceid"]
},
{
"index": "mdl_cont_ins_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_cont_pat_ix",
"usable": true,
"key_parts": ["path", "id"]
}
],
"setup_range_conditions": [],
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "mdl_cont_conins_uix",
"ranges": ["(40) <= (contextlevel) <= (40)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 3588,
"cost": 4311.796108,
"chosen": true
},
{
"index": "mdl_cont_pat_ix",
"ranges": [
"(/1/ ) <= (path) <= (/1/ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿)"
],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 664191,
"cost": 860735.9769,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"cause": "too few roworder scans"
},
"analyzing_index_merge_union": []
},
"group_index_range": {
"chosen": false,
"cause": "not single_table"
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "mdl_cont_conins_uix",
"rows": 3588,
"ranges": ["(40) <= (contextlevel) <= (40)"]
},
"rows_for_plan": 3588,
"cost_for_plan": 4311.796108,
"chosen": true
}
}
},
{
"table": "ctx",
"rowid_filters": [
{
"key": "mdl_cont_conins_uix",
"build_cost": 317.8064737,
"rows": 3588
}
]
},
{
"selectivity_for_indexes": [
{
"index_name": "mdl_cont_conins_uix",
"selectivity_from_index": 0.00270103
},
{
"index_name": "mdl_cont_pat_ix",
"selectivity_from_index": 0.5
}
],
"selectivity_for_columns": [],
"cond_selectivity": 0.001350515
},
{
"table": "ra",
"range_analysis": {
"table_scan": {
"rows": 3886566,
"cost": 799650.2
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_sor_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_rolcon_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_useconrol_ix",
"usable": true,
"key_parts": ["userid", "contextid", "roleid", "id"]
},
{
"index": "mdl_roleassi_comiteuse_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_rol_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_con_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_use_ix",
"usable": true,
"key_parts": ["userid", "id"]
}
],
"best_covering_index_scan": {
"index": "mdl_roleassi_useconrol_ix",
"cost": 788926.2972,
"chosen": true
},
"setup_range_conditions": [],
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "mdl_roleassi_useconrol_ix",
"ranges": ["(78244) <= (userid) <= (78244)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": true,
"rows": 23,
"cost": 4.753983827,
"chosen": true
},
{
"index": "mdl_roleassi_use_ix",
"ranges": ["(78244) <= (userid) <= (78244)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 23,
"cost": 27.74949191,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"cause": "too few roworder scans"
},
"analyzing_index_merge_union": []
},
"group_index_range": {
"chosen": false,
"cause": "not single_table"
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "mdl_roleassi_useconrol_ix",
"rows": 23,
"ranges": ["(78244) <= (userid) <= (78244)"]
},
"rows_for_plan": 23,
"cost_for_plan": 4.753983827,
"chosen": true
}
}
},
{
"table": "ra",
"rowid_filters": [
{
"key": "mdl_roleassi_use_ix",
"build_cost": 0.965655583,
"rows": 23
},
{
"key": "mdl_roleassi_use_ix",
"build_cost": 0.965655583,
"rows": 23
}
]
},
{
"selectivity_for_indexes": [
{
"index_name": "mdl_roleassi_useconrol_ix",
"selectivity_from_index": 5.917821e-6
}
],
"selectivity_for_columns": [],
"cond_selectivity": 5.917821e-6
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "ra",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_roleassi_useconrol_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 0.133983827,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_use_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 23.12949191,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 23,
"cost": 0.133983827,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "ctx",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_cont_conins_uix",
"used_range_estimates": true,
"rows": 3588,
"cost": 3594.176108,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 3588,
"cost": 3594.176108,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 3737,
"cost": 97,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 3737,
"cost": 97,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": [],
"table": "ra",
"rows_for_plan": 23,
"cost_for_plan": 4.733983827,
"rest_of_plan": [
{
"plan_prefix": ["ra"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "ctx",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 23,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_cont_conins_uix",
"used_range_estimates": true,
"rows": 3588,
"cost": 82666.05048,
"chosen": false,
"cause": "cost"
},
{
"access_type": "ref",
"index": "mdl_cont_ins_ix",
"chosen": false,
"cause": "no predicate for first keypart"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 23,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 3737,
"cost": 97,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 3737,
"cost": 97,
"uses_join_buffering": true
}
}
}
]
},
{
"plan_prefix": ["ra"],
"table": "ctx",
"rows_for_plan": 23,
"cost_for_plan": 32.33398383,
"selectivity": 0.001350515,
"estimated_join_cardinality": 0.031061848,
"rest_of_plan": [
{
"plan_prefix": ["ra", "ctx"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 0.031061848,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 0.031061848,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["ra", "ctx"],
"table": "cc",
"rows_for_plan": 0.031061848,
"cost_for_plan": 32.37125804
}
]
}
]
},
{
"plan_prefix": [],
"table": "ctx",
"rows_for_plan": 3588,
"cost_for_plan": 4311.776108,
"pruned_by_cost": true,
"current_cost": 4311.776108,
"best_cost": 32.37125804
},
{
"plan_prefix": [],
"table": "cc",
"rows_for_plan": 3737,
"cost_for_plan": 844.4,
"pruned_by_cost": true,
"current_cost": 844.4,
"best_cost": 32.37125804
}
]
},
{
"best_join_order": ["ra", "ctx", "cc"]
},
{
"substitute_best_equal": {
"condition": "WHERE",
"resulting_condition": "ra.userid = 78244 and ctx.`id` = ra.contextid and cc.`id` = ctx.instanceid and ctx.contextlevel = 40 and ctx.`path` like '/1/%'"
}
},
{
"attaching_conditions_to_tables": {
"attached_conditions_computation": [],
"attached_conditions_summary": [
{
"table": "ra",
"attached": null
},
{
"table": "ctx",
"attached": "ctx.contextlevel = 40 and ctx.`path` like '/1/%'"
},
{
"table": "cc",
"attached": null
}
]
}
}
]
}
},
{
"join_optimization": {
"select_id": 2,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "ctx.`path` like '/1/%' and rc_ra.userid = 78244 and (ctx.`path` = rc_ra_ctx.`path` or ctx.`path` like concat(rc_ra_ctx.`path`,'/%')) and rc_ra_ctx.`id` = rc_ra.contextid and rc_ra.roleid = rc.roleid and rc.contextid = ctx.`id` and cc.`id` = ctx.instanceid and ctx.contextlevel = 40",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "ctx.`path` like '/1/%' and (multiple equal(ctx.`path`, rc_ra_ctx.`path`) or ctx.`path` like concat(rc_ra_ctx.`path`,'/%')) and multiple equal(78244, rc_ra.userid) and multiple equal(rc_ra_ctx.`id`, rc_ra.contextid) and multiple equal(rc_ra.roleid, rc.roleid) and multiple equal(rc.contextid, ctx.`id`) and multiple equal(cc.`id`, ctx.instanceid) and multiple equal(40, ctx.contextlevel)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "ctx.`path` like '/1/%' and (multiple equal(ctx.`path`, rc_ra_ctx.`path`) or ctx.`path` like concat(rc_ra_ctx.`path`,'/%')) and multiple equal(78244, rc_ra.userid) and multiple equal(rc_ra_ctx.`id`, rc_ra.contextid) and multiple equal(rc_ra.roleid, rc.roleid) and multiple equal(rc.contextid, ctx.`id`) and multiple equal(cc.`id`, ctx.instanceid) and multiple equal(40, ctx.contextlevel)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "ctx.`path` like '/1/%' and (multiple equal(ctx.`path`, rc_ra_ctx.`path`) or ctx.`path` like concat(rc_ra_ctx.`path`,'/%')) and multiple equal(78244, rc_ra.userid) and multiple equal(rc_ra_ctx.`id`, rc_ra.contextid) and multiple equal(rc_ra.roleid, rc.roleid) and multiple equal(rc.contextid, ctx.`id`) and multiple equal(cc.`id`, ctx.instanceid) and multiple equal(40, ctx.contextlevel)"
}
]
}
},
{
"table_dependencies": [
{
"table": "cc",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
},
{
"table": "ctx",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": []
},
{
"table": "rc",
"row_may_be_null": false,
"map_bit": 2,
"depends_on_map_bits": []
},
{
"table": "rc_ra",
"row_may_be_null": false,
"map_bit": 3,
"depends_on_map_bits": []
},
{
"table": "rc_ra_ctx",
"row_may_be_null": false,
"map_bit": 4,
"depends_on_map_bits": []
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "cc",
"index": "PRIMARY",
"field": "id",
"equals": "ctx.instanceid",
"null_rejecting": false
},
{
"table": "ctx",
"index": "PRIMARY",
"field": "id",
"equals": "rc.contextid",
"null_rejecting": false
},
{
"table": "ctx",
"index": "mdl_cont_conins_uix",
"field": "contextlevel",
"equals": "40",
"null_rejecting": false
},
{
"table": "ctx",
"index": "mdl_cont_conins_uix",
"field": "instanceid",
"equals": "cc.`id`",
"null_rejecting": false
},
{
"table": "ctx",
"index": "mdl_cont_ins_ix",
"field": "instanceid",
"equals": "cc.`id`",
"null_rejecting": false
},
{
"table": "ctx",
"index": "mdl_cont_ins_ix",
"field": "id",
"equals": "rc.contextid",
"null_rejecting": false
},
{
"table": "rc",
"index": "mdl_rolecapa_rolconcap_uix",
"field": "roleid",
"equals": "rc_ra.roleid",
"null_rejecting": false
},
{
"table": "rc",
"index": "mdl_rolecapa_rolconcap_uix",
"field": "contextid",
"equals": "ctx.`id`",
"null_rejecting": false
},
{
"table": "rc",
"index": "mdl_rolecapa_rol_ix",
"field": "roleid",
"equals": "rc_ra.roleid",
"null_rejecting": false
},
{
"table": "rc",
"index": "mdl_rolecapa_con_ix",
"field": "contextid",
"equals": "ctx.`id`",
"null_rejecting": false
},
{
"table": "rc_ra",
"index": "mdl_roleassi_rolcon_ix",
"field": "roleid",
"equals": "rc.roleid",
"null_rejecting": false
},
{
"table": "rc_ra",
"index": "mdl_roleassi_rolcon_ix",
"field": "contextid",
"equals": "rc_ra_ctx.`id`",
"null_rejecting": false
},
{
"table": "rc_ra",
"index": "mdl_roleassi_useconrol_ix",
"field": "userid",
"equals": "78244",
"null_rejecting": false
},
{
"table": "rc_ra",
"index": "mdl_roleassi_useconrol_ix",
"field": "contextid",
"equals": "rc_ra_ctx.`id`",
"null_rejecting": false
},
{
"table": "rc_ra",
"index": "mdl_roleassi_useconrol_ix",
"field": "roleid",
"equals": "rc.roleid",
"null_rejecting": false
},
{
"table": "rc_ra",
"index": "mdl_roleassi_rol_ix",
"field": "roleid",
"equals": "rc.roleid",
"null_rejecting": false
},
{
"table": "rc_ra",
"index": "mdl_roleassi_con_ix",
"field": "contextid",
"equals": "rc_ra_ctx.`id`",
"null_rejecting": false
},
{
"table": "rc_ra",
"index": "mdl_roleassi_use_ix",
"field": "userid",
"equals": "78244",
"null_rejecting": false
},
{
"table": "rc_ra_ctx",
"index": "PRIMARY",
"field": "id",
"equals": "rc_ra.contextid",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "cc",
"table_scan": {
"rows": 3737,
"cost": 97
}
},
{
"table": "ctx",
"range_analysis": {
"table_scan": {
"rows": 1328382,
"cost": 272253.4
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_cont_conins_uix",
"usable": true,
"key_parts": ["contextlevel", "instanceid"]
},
{
"index": "mdl_cont_ins_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_cont_pat_ix",
"usable": true,
"key_parts": ["path", "id"]
}
],
"setup_range_conditions": [],
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "mdl_cont_conins_uix",
"ranges": ["(40) <= (contextlevel) <= (40)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 3588,
"cost": 4311.796108,
"chosen": true
},
{
"index": "mdl_cont_pat_ix",
"ranges": [
"(/1/ ) <= (path) <= (/1/ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿)"
],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 664191,
"cost": 860735.9769,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"cause": "too few roworder scans"
},
"analyzing_index_merge_union": []
},
"group_index_range": {
"chosen": false,
"cause": "not single_table"
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "mdl_cont_conins_uix",
"rows": 3588,
"ranges": ["(40) <= (contextlevel) <= (40)"]
},
"rows_for_plan": 3588,
"cost_for_plan": 4311.796108,
"chosen": true
}
}
},
{
"table": "ctx",
"rowid_filters": [
{
"key": "mdl_cont_conins_uix",
"build_cost": 317.8064737,
"rows": 3588
}
]
},
{
"selectivity_for_indexes": [
{
"index_name": "mdl_cont_conins_uix",
"selectivity_from_index": 0.00270103
},
{
"index_name": "mdl_cont_pat_ix",
"selectivity_from_index": 0.5
}
],
"selectivity_for_columns": [],
"cond_selectivity": 0.001350515
},
{
"table": "rc",
"table_scan": {
"rows": 34604,
"cost": 225
}
},
{
"table": "rc_ra",
"range_analysis": {
"table_scan": {
"rows": 3886566,
"cost": 799650.2
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_sor_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_rolcon_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_useconrol_ix",
"usable": true,
"key_parts": ["userid", "contextid", "roleid", "id"]
},
{
"index": "mdl_roleassi_comiteuse_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_rol_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_con_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_use_ix",
"usable": true,
"key_parts": ["userid", "id"]
}
],
"best_covering_index_scan": {
"index": "mdl_roleassi_useconrol_ix",
"cost": 788926.2972,
"chosen": true
},
"setup_range_conditions": [],
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "mdl_roleassi_useconrol_ix",
"ranges": ["(78244) <= (userid) <= (78244)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": true,
"rows": 23,
"cost": 4.753983827,
"chosen": true
},
{
"index": "mdl_roleassi_use_ix",
"ranges": ["(78244) <= (userid) <= (78244)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 23,
"cost": 27.74949191,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"cause": "too few roworder scans"
},
"analyzing_index_merge_union": []
},
"group_index_range": {
"chosen": false,
"cause": "not single_table"
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "mdl_roleassi_useconrol_ix",
"rows": 23,
"ranges": ["(78244) <= (userid) <= (78244)"]
},
"rows_for_plan": 23,
"cost_for_plan": 4.753983827,
"chosen": true
}
}
},
{
"table": "rc_ra",
"rowid_filters": [
{
"key": "mdl_roleassi_use_ix",
"build_cost": 0.965655583,
"rows": 23
},
{
"key": "mdl_roleassi_use_ix",
"build_cost": 0.965655583,
"rows": 23
}
]
},
{
"selectivity_for_indexes": [
{
"index_name": "mdl_roleassi_useconrol_ix",
"selectivity_from_index": 5.917821e-6
}
],
"selectivity_for_columns": [],
"cond_selectivity": 5.917821e-6
},
{
"table": "rc_ra_ctx",
"table_scan": {
"rows": 1328382,
"cost": 6575
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "rc_ra",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_roleassi_useconrol_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 0.133983827,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_use_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 23.12949191,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 23,
"cost": 0.133983827,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "ctx",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_cont_conins_uix",
"used_range_estimates": true,
"rows": 3588,
"cost": 3594.176108,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 3588,
"cost": 3594.176108,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 3737,
"cost": 97,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 3737,
"cost": 97,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 34604,
"cost": 225,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 34604,
"cost": 225,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 1328382,
"cost": 6575,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 1328382,
"cost": 6575,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": [],
"table": "rc_ra",
"rows_for_plan": 23,
"cost_for_plan": 4.733983827,
"rest_of_plan": [
{
"plan_prefix": ["rc_ra"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "ctx",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_cont_conins_uix",
"used_range_estimates": true,
"rows": 3588,
"cost": 82666.05048,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 3588,
"cost": 82666.05048,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 3737,
"cost": 97,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 3737,
"cost": 97,
"uses_join_buffering": true
}
}
},
{
"best_access_path": {
"table": "rc",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_rolecapa_rolconcap_uix",
"rows": 1017,
"cost": 2253.651034,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_rolecapa_rol_ix",
"rows": 1017,
"cost": 15552.56828,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 1017,
"cost": 2253.651034,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 23,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 23,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["rc_ra"],
"table": "rc_ra_ctx",
"rows_for_plan": 23,
"cost_for_plan": 32.33398383,
"rest_of_plan": [
{
"plan_prefix": ["rc_ra", "rc_ra_ctx"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 3737,
"cost": 97,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 3737,
"cost": 97,
"uses_join_buffering": true
}
}
},
{
"best_access_path": {
"table": "ctx",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_cont_conins_uix",
"used_range_estimates": true,
"rows": 3588,
"cost": 82666.05048,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 3588,
"cost": 82666.05048,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_rolecapa_rolconcap_uix",
"rows": 1017,
"cost": 2253.651034,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_rolecapa_rol_ix",
"rows": 1017,
"cost": 15552.56828,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 1017,
"cost": 2253.651034,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["rc_ra", "rc_ra_ctx"],
"table": "rc",
"rows_for_plan": 23391,
"cost_for_plan": 6964.185018,
"rest_of_plan": [
{
"plan_prefix": ["rc_ra", "rc_ra_ctx", "rc"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 3737,
"cost": 97,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 3737,
"cost": 97,
"uses_join_buffering": true
}
}
},
{
"best_access_path": {
"table": "ctx",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 23391,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_cont_conins_uix",
"used_range_estimates": true,
"rows": 3588,
"cost": 84071373.34,
"chosen": false,
"cause": "cost"
},
{
"access_type": "ref",
"index": "mdl_cont_ins_ix",
"chosen": false,
"cause": "no predicate for first keypart"
},
{
"access_type": "range",
"resulting_rows": 1794,
"cost": 109249913.6,
"chosen": false
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 23391,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["rc_ra", "rc_ra_ctx", "rc"],
"table": "ctx",
"rows_for_plan": 23391,
"cost_for_plan": 35033.38502,
"selectivity": 0.001350515,
"estimated_join_cardinality": 31.58989959,
"rest_of_plan": [
{
"plan_prefix": ["rc_ra", "rc_ra_ctx", "rc", "ctx"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 31.58989959,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 31.58989959,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["rc_ra", "rc_ra_ctx", "rc", "ctx"],
"table": "cc",
"rows_for_plan": 31.58989959,
"cost_for_plan": 35071.2929
}
]
}
]
},
{
"plan_prefix": ["rc_ra", "rc_ra_ctx"],
"table": "ctx",
"rows_for_plan": 82524,
"cost_for_plan": 99203.18446,
"pruned_by_cost": true,
"current_cost": 99203.18446,
"best_cost": 35071.2929
},
{
"plan_prefix": ["rc_ra", "rc_ra_ctx"],
"table": "cc",
"rows_for_plan": 85951,
"cost_for_plan": 17319.53398,
"rest_of_plan": [
{
"plan_prefix": ["rc_ra", "rc_ra_ctx", "cc"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "rc",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_rolecapa_rolconcap_uix",
"rows": 1017,
"cost": 8421893.916,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_rolecapa_rol_ix",
"rows": 1017,
"cost": 58119947.65,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 1017,
"cost": 8421893.916,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "ctx",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "mdl_cont_conins_uix",
"rows": 1,
"cost": 3737,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_cont_ins_ix",
"rows": 1,
"cost": 171918.7863,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 3737,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["rc_ra", "rc_ra_ctx", "cc"],
"table": "ctx",
"rows_for_plan": 85951,
"cost_for_plan": 38246.73398,
"pruned_by_cost": true,
"current_cost": 38246.73398,
"best_cost": 35071.2929
},
{
"plan_prefix": ["rc_ra", "rc_ra_ctx", "cc"],
"table": "rc",
"rows_for_plan": 87412167,
"cost_for_plan": 25921646.85,
"pruned_by_cost": true,
"current_cost": 25921646.85,
"best_cost": 35071.2929
}
]
}
]
},
{
"plan_prefix": ["rc_ra"],
"table": "rc",
"rows_for_plan": 23391,
"cost_for_plan": 6936.585018,
"pruned_by_heuristic": true
},
{
"plan_prefix": ["rc_ra"],
"table": "ctx",
"rows_for_plan": 82524,
"cost_for_plan": 99175.58446,
"pruned_by_cost": true,
"current_cost": 99175.58446,
"best_cost": 35071.2929
},
{
"plan_prefix": ["rc_ra"],
"table": "cc",
"rows_for_plan": 85951,
"cost_for_plan": 17291.93398,
"pruned_by_heuristic": true
}
]
},
{
"plan_prefix": [],
"table": "ctx",
"rows_for_plan": 3588,
"cost_for_plan": 4311.776108,
"selectivity": 0.5,
"estimated_join_cardinality": 1794,
"rest_of_plan": [
{
"plan_prefix": ["ctx"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "rc_ra",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_roleassi_useconrol_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 240.366985,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_use_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 41494.30849,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 23,
"cost": 240.366985,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 1794,
"chosen": true
},
{
"access_type": "scan",
"resulting_rows": 3737,
"cost": 97,
"chosen": false
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 1794,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_rolecapa_rolconcap_uix",
"chosen": false,
"cause": "no predicate for first keypart"
},
{
"access_type": "ref",
"index": "mdl_rolecapa_con_ix",
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 1,
"cost": 3588.350369,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 1328382,
"cost": 6575,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 1328382,
"cost": 6575,
"uses_join_buffering": true
}
}
}
]
},
{
"plan_prefix": ["ctx"],
"table": "cc",
"rows_for_plan": 1794,
"cost_for_plan": 6464.576108,
"rest_of_plan": [
{
"plan_prefix": ["ctx", "cc"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 1328382,
"cost": 6575,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 1328382,
"cost": 6575,
"uses_join_buffering": true
}
}
},
{
"best_access_path": {
"table": "rc_ra",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_roleassi_useconrol_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 240.366985,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_use_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 41494.30849,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 23,
"cost": 240.366985,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_rolecapa_rolconcap_uix",
"chosen": false,
"cause": "no predicate for first keypart"
},
{
"access_type": "ref",
"index": "mdl_rolecapa_con_ix",
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 1,
"cost": 3588.350369,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["ctx", "cc"],
"table": "rc",
"rows_for_plan": 1794,
"cost_for_plan": 10411.72648,
"rest_of_plan": [
{
"plan_prefix": ["ctx", "cc", "rc"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "rc_ra",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_roleassi_rolcon_ix",
"rowid_filter_skipped": "worst/max seeks clipping",
"rows": 138805,
"cost": 120281713.5,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_useconrol_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 240.366985,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_rol_ix",
"rowid_filter_skipped": "worst/max seeks clipping",
"rows": 138805,
"cost": 120257397,
"chosen": false,
"cause": "cost"
},
{
"access_type": "ref",
"index": "mdl_roleassi_use_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 41494.30849,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 23,
"cost": 240.366985,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 1328382,
"cost": 6575,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 1328382,
"cost": 6575,
"uses_join_buffering": true
}
}
}
]
},
{
"plan_prefix": ["ctx", "cc", "rc"],
"table": "rc_ra",
"rows_for_plan": 41262,
"cost_for_plan": 18904.49346,
"rest_of_plan": [
{
"plan_prefix": ["ctx", "cc", "rc", "rc_ra"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 41262,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 41262,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["ctx", "cc", "rc", "rc_ra"],
"table": "rc_ra_ctx",
"rows_for_plan": 41262,
"cost_for_plan": 68418.89346,
"pruned_by_cost": true,
"current_cost": 68418.89346,
"best_cost": 35071.2929
}
]
},
{
"plan_prefix": ["ctx", "cc", "rc"],
"table": "rc_ra_ctx",
"rows_for_plan": 2383117308,
"cost_for_plan": 476640448.3,
"pruned_by_cost": true,
"current_cost": 476640448.3,
"best_cost": 35071.2929
}
]
},
{
"plan_prefix": ["ctx", "cc"],
"table": "rc_ra",
"rows_for_plan": 41262,
"cost_for_plan": 14957.34309,
"pruned_by_heuristic": true
},
{
"plan_prefix": ["ctx", "cc"],
"table": "rc_ra_ctx",
"rows_for_plan": 2383117308,
"cost_for_plan": 476636501.2,
"pruned_by_cost": true,
"current_cost": 476636501.2,
"best_cost": 35071.2929
}
]
},
{
"plan_prefix": ["ctx"],
"table": "rc",
"rows_for_plan": 1794,
"cost_for_plan": 8258.926477,
"pruned_by_heuristic": true
},
{
"plan_prefix": ["ctx"],
"table": "rc_ra",
"rows_for_plan": 41262,
"cost_for_plan": 12804.54309,
"pruned_by_heuristic": true
},
{
"plan_prefix": ["ctx"],
"table": "rc_ra_ctx",
"rows_for_plan": 2383117308,
"cost_for_plan": 476634348.4,
"pruned_by_cost": true,
"current_cost": 476634348.4,
"best_cost": 35071.2929
}
]
},
{
"plan_prefix": [],
"table": "cc",
"rows_for_plan": 3737,
"cost_for_plan": 844.4,
"rest_of_plan": [
{
"plan_prefix": ["cc"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "ctx",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "mdl_cont_conins_uix",
"rows": 1,
"cost": 3737,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_cont_ins_ix",
"rows": 1,
"cost": 7474.729838,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 3737,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc_ra",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_roleassi_useconrol_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 500.6975603,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_use_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 86434.91128,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 23,
"cost": 500.6975603,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 34604,
"cost": 225,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 34604,
"cost": 225,
"uses_join_buffering": true
}
}
},
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 1328382,
"cost": 6575,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 1328382,
"cost": 6575,
"uses_join_buffering": true
}
}
}
]
},
{
"plan_prefix": ["cc"],
"table": "ctx",
"rows_for_plan": 3737,
"cost_for_plan": 5328.8,
"selectivity": 0.5,
"estimated_join_cardinality": 1868.5,
"rest_of_plan": [
{
"plan_prefix": ["cc", "ctx"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 1328382,
"cost": 6575,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 1328382,
"cost": 6575,
"uses_join_buffering": true
}
}
},
{
"best_access_path": {
"table": "rc_ra",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_roleassi_useconrol_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 250.3487801,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_use_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 43217.45564,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 23,
"cost": 250.3487801,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_rolecapa_rolconcap_uix",
"chosen": false,
"cause": "no predicate for first keypart"
},
{
"access_type": "ref",
"index": "mdl_rolecapa_con_ix",
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 1,
"cost": 3737.364919,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["cc", "ctx"],
"table": "rc",
"rows_for_plan": 1868.5,
"cost_for_plan": 9439.864919,
"rest_of_plan": [
{
"plan_prefix": ["cc", "ctx", "rc"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "rc_ra",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_roleassi_rolcon_ix",
"rowid_filter_skipped": "worst/max seeks clipping",
"rows": 138805,
"cost": 125276689.9,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_useconrol_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 250.3487801,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_rol_ix",
"rowid_filter_skipped": "worst/max seeks clipping",
"rows": 138805,
"cost": 125251363.6,
"chosen": false,
"cause": "cost"
},
{
"access_type": "ref",
"index": "mdl_roleassi_use_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 43217.45564,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 23,
"cost": 250.3487801,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 1328382,
"cost": 6575,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 1328382,
"cost": 6575,
"uses_join_buffering": true
}
}
}
]
},
{
"plan_prefix": ["cc", "ctx", "rc"],
"table": "rc_ra",
"rows_for_plan": 42975.5,
"cost_for_plan": 18285.3137,
"rest_of_plan": [
{
"plan_prefix": ["cc", "ctx", "rc", "rc_ra"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 42975.5,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 42975.5,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["cc", "ctx", "rc", "rc_ra"],
"table": "rc_ra_ctx",
"rows_for_plan": 42975.5,
"cost_for_plan": 69855.9137,
"pruned_by_cost": true,
"current_cost": 69855.9137,
"best_cost": 35071.2929
}
]
},
{
"plan_prefix": ["cc", "ctx", "rc"],
"table": "rc_ra_ctx",
"rows_for_plan": 2482081767,
"cost_for_plan": 496432368.3,
"pruned_by_cost": true,
"current_cost": 496432368.3,
"best_cost": 35071.2929
}
]
},
{
"plan_prefix": ["cc", "ctx"],
"table": "rc_ra",
"rows_for_plan": 42975.5,
"cost_for_plan": 14174.24878,
"pruned_by_heuristic": true
},
{
"plan_prefix": ["cc", "ctx"],
"table": "rc_ra_ctx",
"rows_for_plan": 2482081767,
"cost_for_plan": 496428257.2,
"pruned_by_cost": true,
"current_cost": 496428257.2,
"best_cost": 35071.2929
}
]
},
{
"plan_prefix": ["cc"],
"table": "rc_ra",
"rows_for_plan": 85951,
"cost_for_plan": 18535.29756,
"pruned_by_heuristic": true
},
{
"plan_prefix": ["cc"],
"table": "rc",
"rows_for_plan": 129315148,
"cost_for_plan": 25864099,
"pruned_by_cost": true,
"current_cost": 25864099,
"best_cost": 35071.2929
},
{
"plan_prefix": ["cc"],
"table": "rc_ra_ctx",
"rows_for_plan": 4964163534,
"cost_for_plan": 992840126.2,
"pruned_by_cost": true,
"current_cost": 992840126.2,
"best_cost": 35071.2929
}
]
},
{
"plan_prefix": [],
"table": "rc",
"rows_for_plan": 34604,
"cost_for_plan": 7145.8,
"rest_of_plan": [
{
"plan_prefix": ["rc"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "ctx",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 34604,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_cont_conins_uix",
"used_range_estimates": true,
"rows": 3588,
"cost": 124372870,
"chosen": false,
"cause": "cost"
},
{
"access_type": "ref",
"index": "mdl_cont_ins_ix",
"chosen": false,
"cause": "no predicate for first keypart"
},
{
"access_type": "range",
"resulting_rows": 1794,
"cost": 161621307.7,
"chosen": false
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 34604,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 3737,
"cost": 97,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 3737,
"cost": 97,
"uses_join_buffering": true
}
}
},
{
"best_access_path": {
"table": "rc_ra",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_roleassi_rolcon_ix",
"rowid_filter_skipped": "worst/max seeks clipping",
"rows": 138805,
"cost": 2320082728,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_useconrol_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 4636.376338,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_rol_ix",
"rowid_filter_skipped": "worst/max seeks clipping",
"rows": 138805,
"cost": 2319613693,
"chosen": false,
"cause": "cost"
},
{
"access_type": "ref",
"index": "mdl_roleassi_use_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 800372.9382,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 23,
"cost": 4636.376338,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 1328382,
"cost": 6575,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 1328382,
"cost": 6575,
"uses_join_buffering": true
}
}
}
]
},
{
"plan_prefix": ["rc"],
"table": "ctx",
"rows_for_plan": 34604,
"cost_for_plan": 48670.6,
"pruned_by_cost": true,
"current_cost": 48670.6,
"best_cost": 35071.2929
},
{
"plan_prefix": ["rc"],
"table": "rc_ra",
"rows_for_plan": 795892,
"cost_for_plan": 170960.5763,
"pruned_by_cost": true,
"current_cost": 170960.5763,
"best_cost": 35071.2929
},
{
"plan_prefix": ["rc"],
"table": "cc",
"rows_for_plan": 129315148,
"cost_for_plan": 25870272.4,
"pruned_by_cost": true,
"current_cost": 25870272.4,
"best_cost": 35071.2929
},
{
"plan_prefix": ["rc"],
"table": "rc_ra_ctx",
"rows_for_plan": 45967330728,
"cost_for_plan": 9193479866,
"pruned_by_cost": true,
"current_cost": 9193479866,
"best_cost": 35071.2929
}
]
},
{
"plan_prefix": [],
"table": "rc_ra_ctx",
"rows_for_plan": 1328382,
"cost_for_plan": 272251.4,
"pruned_by_cost": true,
"current_cost": 272251.4,
"best_cost": 35071.2929
}
]
},
{
"best_join_order": ["rc_ra", "rc_ra_ctx", "rc", "ctx", "cc"]
},
{
"substitute_best_equal": {
"condition": "WHERE",
"resulting_condition": "rc_ra.userid = 78244 and rc_ra_ctx.`id` = rc_ra.contextid and rc.roleid = rc_ra.roleid and ctx.`id` = rc.contextid and cc.`id` = ctx.instanceid and ctx.contextlevel = 40 and ctx.`path` like '/1/%' and (ctx.`path` = rc_ra_ctx.`path` or ctx.`path` like concat(rc_ra_ctx.`path`,'/%'))"
}
},
{
"attaching_conditions_to_tables": {
"attached_conditions_computation": [],
"attached_conditions_summary": [
{
"table": "rc_ra",
"attached": null
},
{
"table": "rc_ra_ctx",
"attached": null
},
{
"table": "rc",
"attached": null
},
{
"table": "ctx",
"attached": "ctx.contextlevel = 40 and ctx.`path` like '/1/%' and (ctx.`path` = rc_ra_ctx.`path` or ctx.`path` like concat(rc_ra_ctx.`path`,'/%'))"
},
{
"table": "cc",
"attached": null
}
]
}
}
]
}
},
{
"join_execution": {
"select_id": 1,
"steps": []
}
},
{
"join_execution": {
"select_id": 2,
"steps": []
}
},
{
"join_preparation": {
"select_id": "fake",
"steps": [
{
"expanded_query": "select `id` AS `id`,`name` AS `name`,idnumber AS idnumber,parent AS parent,sortorder AS sortorder,coursecount AS coursecount,`visible` AS `visible`,depth AS depth,`path` AS `path`,ctxid AS ctxid,ctxpath AS ctxpath,ctxdepth AS ctxdepth,ctxlevel AS ctxlevel,ctxinstance AS ctxinstance,ctxlocked AS ctxlocked from dual"
}
]
}
},
{
"join_optimization": {
"select_id": "fake",
"steps": [
{
"table_dependencies": [
{
"table": "union",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
}
]
},
{
"rows_estimation": [
{
"table": "union",
"table_scan": {
"rows": 3,
"cost": 10.15
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "union",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 3,
"cost": 10.15,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 3,
"cost": 10.15,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": [],
"table": "union",
"rows_for_plan": 3,
"cost_for_plan": 10.75
}
]
},
{
"best_join_order": ["union"]
},
{
"attaching_conditions_to_tables": {
"attached_conditions_computation": [],
"attached_conditions_summary": [
{
"table": "union",
"attached": null
}
]
}
}
]
}
},
{
"join_execution": {
"select_id": "fake",
"steps": []
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0,002 sec)
MariaDB [arche_prod]> set @@optimizer_adjust_secondary_key_costs='fix_card_multiplier';
Query OK, 0 rows affected (0,000 sec)
MariaDB [arche_prod]> ANALYZE FORMAT=JSON SELECT cc.id,cc.name,cc.idnumber,cc.parent,cc.sortorder,cc.coursecount,cc.visible,cc.depth,cc.path, ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, ctx.locked AS ctxlocked FROM mdl_course_categories cc JOIN mdl_context ctx ON cc.id = ctx.instanceid AND ctx.contextlevel = '40' JOIN mdl_role_assignments ra ON ra.contextid = ctx.id WHERE ctx.path LIKE '/1/%' AND
ra.userid = '78244' UNION SELECT cc.id,cc.name,cc.idnumber,cc.parent,cc.sortorder,cc.coursecount,cc.visible,cc.depth,cc.path, ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, ctx.locked AS ctxlocked FROM mdl_course_categories cc JOIN mdl_context ctx ON cc.id = ctx.instanceid AND ctx.contextlevel = '40' JOIN mdl_role_capabilities rc ON rc.contextid = ctx.id JOIN mdl_role_assignments rc_ra ON rc_ra.roleid = rc.roleid JOIN mdl_context rc_ra_ctx ON rc_ra_ctx.id = rc_ra.contextid WHERE ctx.path LIKE '/1/%' AND rc_ra.userid = '78244' AND (ctx.path = rc_ra_ctx.path OR ctx.path LIKE CONCAT(rc_ra_ctx.path, '/%'));
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ANALYZE |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"query_optimization": {
"r_total_time_ms": 1.076939021
},
"query_block": {
"union_result": {
"table_name": "<union1,2>",
"access_type": "ALL",
"r_loops": 1,
"r_rows": 3,
"query_specifications": [
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.127017758,
"nested_loop": [
{
"table": {
"table_name": "ra",
"access_type": "ref",
"possible_keys": [
"mdl_roleassi_useconrol_ix",
"mdl_roleassi_con_ix",
"mdl_roleassi_use_ix"
],
"key": "mdl_roleassi_useconrol_ix",
"key_length": "8",
"used_key_parts": ["userid"],
"ref": ["const"],
"r_loops": 1,
"rows": 23,
"r_rows": 23,
"r_table_time_ms": 0.020298557,
"r_other_time_ms": 0.009611612,
"r_engine_stats": {
"pages_accessed": 3
},
"filtered": 100,
"r_filtered": 100,
"using_index": true
}
},
{
"table": {
"table_name": "ctx",
"access_type": "eq_ref",
"possible_keys": [
"PRIMARY",
"mdl_cont_conins_uix",
"mdl_cont_ins_ix",
"mdl_cont_pat_ix"
],
"key": "PRIMARY",
"key_length": "8",
"used_key_parts": ["id"],
"ref": ["arche_prod.ra.contextid"],
"r_loops": 23,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.087318648,
"r_other_time_ms": 0.003246965,
"r_engine_stats": {
"pages_accessed": 66
},
"filtered": 0.135051519,
"r_filtered": 0,
"attached_condition": "ctx.contextlevel = 40 and ctx.`path` like '/1/%'"
}
},
{
"table": {
"table_name": "cc",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "8",
"used_key_parts": ["id"],
"ref": ["arche_prod.ctx.instanceid"],
"r_loops": 0,
"rows": 1,
"r_rows": null,
"r_engine_stats": {},
"filtered": 100,
"r_filtered": null
}
}
]
}
},
{
"query_block": {
"select_id": 2,
"operation": "UNION",
"r_loops": 1,
"r_total_time_ms": 107.6887928,
"nested_loop": [
{
"table": {
"table_name": "ctx",
"access_type": "ref",
"possible_keys": [
"PRIMARY",
"mdl_cont_conins_uix",
"mdl_cont_ins_ix",
"mdl_cont_pat_ix"
],
"key": "mdl_cont_conins_uix",
"key_length": "8",
"used_key_parts": ["contextlevel"],
"ref": ["const"],
"r_loops": 1,
"rows": 3588,
"r_rows": 3588,
"r_table_time_ms": 20.68727008,
"r_other_time_ms": 0.832023312,
"r_engine_stats": {
"pages_accessed": 10774,
"pages_read_count": 13,
"pages_read_time_ms": 13.53580911
},
"filtered": 50,
"r_filtered": 100,
"attached_condition": "ctx.`path` like '/1/%'"
}
},
{
"table": {
"table_name": "cc",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "8",
"used_key_parts": ["id"],
"ref": ["arche_prod.ctx.instanceid"],
"r_loops": 3588,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 34.17940458,
"r_other_time_ms": 0.329980613,
"r_engine_stats": {
"pages_accessed": 7240,
"pages_read_count": 30,
"pages_read_time_ms": 29.03216599
},
"filtered": 100,
"r_filtered": 100
}
},
{
"table": {
"table_name": "rc",
"access_type": "ref",
"possible_keys": [
"mdl_rolecapa_rolconcap_uix",
"mdl_rolecapa_rol_ix",
"mdl_rolecapa_con_ix"
],
"key": "mdl_rolecapa_con_ix",
"key_length": "8",
"used_key_parts": ["contextid"],
"ref": ["arche_prod.ctx.id"],
"r_loops": 3588,
"rows": 1,
"r_rows": 0.011984392,
"r_table_time_ms": 50.60651977,
"r_other_time_ms": 0.239311047,
"r_engine_stats": {
"pages_accessed": 7305,
"pages_read_count": 54,
"pages_read_time_ms": 46.29708561
},
"filtered": 100,
"r_filtered": 100
}
},
{
"table": {
"table_name": "rc_ra",
"access_type": "ref",
"possible_keys": [
"mdl_roleassi_rolcon_ix",
"mdl_roleassi_useconrol_ix",
"mdl_roleassi_rol_ix",
"mdl_roleassi_con_ix",
"mdl_roleassi_use_ix"
],
"key": "mdl_roleassi_useconrol_ix",
"key_length": "8",
"used_key_parts": ["userid"],
"ref": ["const"],
"r_loops": 43,
"rows": 9,
"r_rows": 23,
"r_table_time_ms": 0.381131019,
"r_other_time_ms": 0.114880729,
"r_engine_stats": {
"pages_accessed": 129
},
"filtered": 100,
"r_filtered": 7.684529828,
"attached_condition": "rc_ra.roleid = rc.roleid",
"using_index": true
}
},
{
"table": {
"table_name": "rc_ra_ctx",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY", "mdl_cont_pat_ix"],
"key": "PRIMARY",
"key_length": "8",
"used_key_parts": ["id"],
"ref": ["arche_prod.rc_ra.contextid"],
"r_loops": 76,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.168489289,
"r_other_time_ms": 0.147119767,
"r_engine_stats": {
"pages_accessed": 222
},
"filtered": 100,
"r_filtered": 6.578947368,
"attached_condition": "rc_ra_ctx.`path` = ctx.`path` or ctx.`path` like concat(rc_ra_ctx.`path`,'/%')"
}
}
]
}
}
]
}
}
} |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,109 sec)
MariaDB [arche_prod]> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
QUERY: ANALYZE FORMAT=JSON SELECT cc.id,cc.name,cc.idnumber,cc.parent,cc.sortorder,cc.coursecount,cc.visible,cc.depth,cc.path, ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, ctx.locked AS ctxlocked FROM mdl_course_categories cc JOIN mdl_context ctx ON cc.id = ctx.instanceid AND ctx.contextlevel = '40' JOIN mdl_role_assignments ra ON ra.contextid = ctx.id WHERE ctx.path LIKE '/1/%' AND ra.userid = '78244' UNION SELECT cc.id,cc.name,cc.idnumber,cc.parent,cc.sortorder,cc.coursecount,cc.visible,cc.depth,cc.path, ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, ctx.locked AS ctxlocked FROM mdl_course_categories cc JOIN mdl_context ctx ON cc.id = ctx.instanceid AND ctx.contextlevel = '40' JOIN mdl_role_capabilities rc ON rc.contextid = ctx.id JOIN mdl_role_assignments rc_ra ON rc_ra.roleid = rc.roleid JOIN mdl_context rc_ra_ctx ON rc_ra_ctx.id = rc_ra.contextid WHERE ctx.path LIKE '/1/%' AND rc_ra.userid = '78244' AND (ctx.path = rc_ra_ctx.path OR ctx.path LIKE CONCAT(rc_ra_ctx.path, '/%'))
TRACE: {
"steps": [
{
"join_preparation": {
"select_id": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select cc.`id` AS `id`,cc.`name` AS `name`,cc.idnumber AS idnumber,cc.parent AS parent,cc.sortorder AS sortorder,cc.coursecount AS coursecount,cc.`visible` AS `visible`,cc.depth AS depth,cc.`path` AS `path`,ctx.`id` AS ctxid,ctx.`path` AS ctxpath,ctx.depth AS ctxdepth,ctx.contextlevel AS ctxlevel,ctx.instanceid AS ctxinstance,ctx.`locked` AS ctxlocked from ((mdl_course_categories cc join mdl_context ctx on(cc.`id` = ctx.instanceid and ctx.contextlevel = 40)) join mdl_role_assignments ra on(ra.contextid = ctx.`id`)) where ctx.`path` like '/1/%' and ra.userid = 78244"
}
]
}
},
{
"join_preparation": {
"select_id": 2,
"steps": [
{
"expanded_query": "/* select#2 */ select cc.`id` AS `id`,cc.`name` AS `name`,cc.idnumber AS idnumber,cc.parent AS parent,cc.sortorder AS sortorder,cc.coursecount AS coursecount,cc.`visible` AS `visible`,cc.depth AS depth,cc.`path` AS `path`,ctx.`id` AS ctxid,ctx.`path` AS ctxpath,ctx.depth AS ctxdepth,ctx.contextlevel AS ctxlevel,ctx.instanceid AS ctxinstance,ctx.`locked` AS ctxlocked from ((((mdl_course_categories cc join mdl_context ctx on(cc.`id` = ctx.instanceid and ctx.contextlevel = 40)) join mdl_role_capabilities rc on(rc.contextid = ctx.`id`)) join mdl_role_assignments rc_ra on(rc_ra.roleid = rc.roleid)) join mdl_context rc_ra_ctx on(rc_ra_ctx.`id` = rc_ra.contextid)) where ctx.`path` like '/1/%' and rc_ra.userid = 78244 and (ctx.`path` = rc_ra_ctx.`path` or ctx.`path` like concat(rc_ra_ctx.`path`,'/%'))"
}
]
}
},
{
"join_optimization": {
"select_id": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "ctx.`path` like '/1/%' and ra.userid = 78244 and ra.contextid = ctx.`id` and cc.`id` = ctx.instanceid and ctx.contextlevel = 40",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "ctx.`path` like '/1/%' and multiple equal(78244, ra.userid) and multiple equal(ra.contextid, ctx.`id`) and multiple equal(cc.`id`, ctx.instanceid) and multiple equal(40, ctx.contextlevel)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "ctx.`path` like '/1/%' and multiple equal(78244, ra.userid) and multiple equal(ra.contextid, ctx.`id`) and multiple equal(cc.`id`, ctx.instanceid) and multiple equal(40, ctx.contextlevel)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "ctx.`path` like '/1/%' and multiple equal(78244, ra.userid) and multiple equal(ra.contextid, ctx.`id`) and multiple equal(cc.`id`, ctx.instanceid) and multiple equal(40, ctx.contextlevel)"
}
]
}
},
{
"table_dependencies": [
{
"table": "cc",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
},
{
"table": "ctx",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": []
},
{
"table": "ra",
"row_may_be_null": false,
"map_bit": 2,
"depends_on_map_bits": []
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "cc",
"index": "PRIMARY",
"field": "id",
"equals": "ctx.instanceid",
"null_rejecting": false
},
{
"table": "ctx",
"index": "PRIMARY",
"field": "id",
"equals": "ra.contextid",
"null_rejecting": false
},
{
"table": "ctx",
"index": "mdl_cont_conins_uix",
"field": "contextlevel",
"equals": "40",
"null_rejecting": false
},
{
"table": "ctx",
"index": "mdl_cont_conins_uix",
"field": "instanceid",
"equals": "cc.`id`",
"null_rejecting": false
},
{
"table": "ctx",
"index": "mdl_cont_ins_ix",
"field": "instanceid",
"equals": "cc.`id`",
"null_rejecting": false
},
{
"table": "ctx",
"index": "mdl_cont_ins_ix",
"field": "id",
"equals": "ra.contextid",
"null_rejecting": false
},
{
"table": "ra",
"index": "mdl_roleassi_useconrol_ix",
"field": "userid",
"equals": "78244",
"null_rejecting": false
},
{
"table": "ra",
"index": "mdl_roleassi_useconrol_ix",
"field": "contextid",
"equals": "ctx.`id`",
"null_rejecting": false
},
{
"table": "ra",
"index": "mdl_roleassi_con_ix",
"field": "contextid",
"equals": "ctx.`id`",
"null_rejecting": false
},
{
"table": "ra",
"index": "mdl_roleassi_use_ix",
"field": "userid",
"equals": "78244",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "cc",
"table_scan": {
"rows": 3737,
"cost": 97
}
},
{
"table": "ctx",
"range_analysis": {
"table_scan": {
"rows": 1328382,
"cost": 272253.4
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_cont_conins_uix",
"usable": true,
"key_parts": ["contextlevel", "instanceid"]
},
{
"index": "mdl_cont_ins_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_cont_pat_ix",
"usable": true,
"key_parts": ["path", "id"]
}
],
"setup_range_conditions": [],
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "mdl_cont_conins_uix",
"ranges": ["(40) <= (contextlevel) <= (40)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 3588,
"cost": 4311.796108,
"chosen": true
},
{
"index": "mdl_cont_pat_ix",
"ranges": [
"(/1/ ) <= (path) <= (/1/ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿)"
],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 664191,
"cost": 860735.9769,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"cause": "too few roworder scans"
},
"analyzing_index_merge_union": []
},
"group_index_range": {
"chosen": false,
"cause": "not single_table"
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "mdl_cont_conins_uix",
"rows": 3588,
"ranges": ["(40) <= (contextlevel) <= (40)"]
},
"rows_for_plan": 3588,
"cost_for_plan": 4311.796108,
"chosen": true
}
}
},
{
"table": "ctx",
"rowid_filters": [
{
"key": "mdl_cont_conins_uix",
"build_cost": 317.8064737,
"rows": 3588
}
]
},
{
"selectivity_for_indexes": [
{
"index_name": "mdl_cont_conins_uix",
"selectivity_from_index": 0.00270103
},
{
"index_name": "mdl_cont_pat_ix",
"selectivity_from_index": 0.5
}
],
"selectivity_for_columns": [],
"cond_selectivity": 0.001350515
},
{
"table": "ra",
"range_analysis": {
"table_scan": {
"rows": 3886566,
"cost": 799650.2
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_sor_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_rolcon_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_useconrol_ix",
"usable": true,
"key_parts": ["userid", "contextid", "roleid", "id"]
},
{
"index": "mdl_roleassi_comiteuse_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_rol_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_con_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_use_ix",
"usable": true,
"key_parts": ["userid", "id"]
}
],
"best_covering_index_scan": {
"index": "mdl_roleassi_useconrol_ix",
"cost": 788926.2972,
"chosen": true
},
"setup_range_conditions": [],
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "mdl_roleassi_useconrol_ix",
"ranges": ["(78244) <= (userid) <= (78244)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": true,
"rows": 23,
"cost": 4.753983827,
"chosen": true
},
{
"index": "mdl_roleassi_use_ix",
"ranges": ["(78244) <= (userid) <= (78244)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 23,
"cost": 27.74949191,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"cause": "too few roworder scans"
},
"analyzing_index_merge_union": []
},
"group_index_range": {
"chosen": false,
"cause": "not single_table"
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "mdl_roleassi_useconrol_ix",
"rows": 23,
"ranges": ["(78244) <= (userid) <= (78244)"]
},
"rows_for_plan": 23,
"cost_for_plan": 4.753983827,
"chosen": true
}
}
},
{
"table": "ra",
"rowid_filters": [
{
"key": "mdl_roleassi_use_ix",
"build_cost": 0.965655583,
"rows": 23
},
{
"key": "mdl_roleassi_use_ix",
"build_cost": 0.965655583,
"rows": 23
}
]
},
{
"selectivity_for_indexes": [
{
"index_name": "mdl_roleassi_useconrol_ix",
"selectivity_from_index": 5.917821e-6
}
],
"selectivity_for_columns": [],
"cond_selectivity": 5.917821e-6
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "ra",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_roleassi_useconrol_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 0.133983827,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_use_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 23.12949191,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 23,
"cost": 0.133983827,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "ctx",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_cont_conins_uix",
"used_range_estimates": true,
"rows": 3588,
"cost": 3594.176108,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 3588,
"cost": 3594.176108,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 3737,
"cost": 97,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 3737,
"cost": 97,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": [],
"table": "ra",
"rows_for_plan": 23,
"cost_for_plan": 4.733983827,
"rest_of_plan": [
{
"plan_prefix": ["ra"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "ctx",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 23,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_cont_conins_uix",
"used_range_estimates": true,
"rows": 3588,
"cost": 82666.05048,
"chosen": false,
"cause": "cost"
},
{
"access_type": "ref",
"index": "mdl_cont_ins_ix",
"chosen": false,
"cause": "no predicate for first keypart"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 23,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 3737,
"cost": 97,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 3737,
"cost": 97,
"uses_join_buffering": true
}
}
}
]
},
{
"plan_prefix": ["ra"],
"table": "ctx",
"rows_for_plan": 23,
"cost_for_plan": 32.33398383,
"selectivity": 0.001350515,
"estimated_join_cardinality": 0.031061848,
"rest_of_plan": [
{
"plan_prefix": ["ra", "ctx"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 0.031061848,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 0.031061848,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["ra", "ctx"],
"table": "cc",
"rows_for_plan": 0.031061848,
"cost_for_plan": 32.37125804
}
]
}
]
},
{
"plan_prefix": [],
"table": "ctx",
"rows_for_plan": 3588,
"cost_for_plan": 4311.776108,
"pruned_by_cost": true,
"current_cost": 4311.776108,
"best_cost": 32.37125804
},
{
"plan_prefix": [],
"table": "cc",
"rows_for_plan": 3737,
"cost_for_plan": 844.4,
"pruned_by_cost": true,
"current_cost": 844.4,
"best_cost": 32.37125804
}
]
},
{
"best_join_order": ["ra", "ctx", "cc"]
},
{
"substitute_best_equal": {
"condition": "WHERE",
"resulting_condition": "ra.userid = 78244 and ctx.`id` = ra.contextid and cc.`id` = ctx.instanceid and ctx.contextlevel = 40 and ctx.`path` like '/1/%'"
}
},
{
"attaching_conditions_to_tables": {
"attached_conditions_computation": [],
"attached_conditions_summary": [
{
"table": "ra",
"attached": null
},
{
"table": "ctx",
"attached": "ctx.contextlevel = 40 and ctx.`path` like '/1/%'"
},
{
"table": "cc",
"attached": null
}
]
}
}
]
}
},
{
"join_optimization": {
"select_id": 2,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "ctx.`path` like '/1/%' and rc_ra.userid = 78244 and (ctx.`path` = rc_ra_ctx.`path` or ctx.`path` like concat(rc_ra_ctx.`path`,'/%')) and rc_ra_ctx.`id` = rc_ra.contextid and rc_ra.roleid = rc.roleid and rc.contextid = ctx.`id` and cc.`id` = ctx.instanceid and ctx.contextlevel = 40",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "ctx.`path` like '/1/%' and (multiple equal(ctx.`path`, rc_ra_ctx.`path`) or ctx.`path` like concat(rc_ra_ctx.`path`,'/%')) and multiple equal(78244, rc_ra.userid) and multiple equal(rc_ra_ctx.`id`, rc_ra.contextid) and multiple equal(rc_ra.roleid, rc.roleid) and multiple equal(rc.contextid, ctx.`id`) and multiple equal(cc.`id`, ctx.instanceid) and multiple equal(40, ctx.contextlevel)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "ctx.`path` like '/1/%' and (multiple equal(ctx.`path`, rc_ra_ctx.`path`) or ctx.`path` like concat(rc_ra_ctx.`path`,'/%')) and multiple equal(78244, rc_ra.userid) and multiple equal(rc_ra_ctx.`id`, rc_ra.contextid) and multiple equal(rc_ra.roleid, rc.roleid) and multiple equal(rc.contextid, ctx.`id`) and multiple equal(cc.`id`, ctx.instanceid) and multiple equal(40, ctx.contextlevel)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "ctx.`path` like '/1/%' and (multiple equal(ctx.`path`, rc_ra_ctx.`path`) or ctx.`path` like concat(rc_ra_ctx.`path`,'/%')) and multiple equal(78244, rc_ra.userid) and multiple equal(rc_ra_ctx.`id`, rc_ra.contextid) and multiple equal(rc_ra.roleid, rc.roleid) and multiple equal(rc.contextid, ctx.`id`) and multiple equal(cc.`id`, ctx.instanceid) and multiple equal(40, ctx.contextlevel)"
}
]
}
},
{
"table_dependencies": [
{
"table": "cc",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
},
{
"table": "ctx",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": []
},
{
"table": "rc",
"row_may_be_null": false,
"map_bit": 2,
"depends_on_map_bits": []
},
{
"table": "rc_ra",
"row_may_be_null": false,
"map_bit": 3,
"depends_on_map_bits": []
},
{
"table": "rc_ra_ctx",
"row_may_be_null": false,
"map_bit": 4,
"depends_on_map_bits": []
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "cc",
"index": "PRIMARY",
"field": "id",
"equals": "ctx.instanceid",
"null_rejecting": false
},
{
"table": "ctx",
"index": "PRIMARY",
"field": "id",
"equals": "rc.contextid",
"null_rejecting": false
},
{
"table": "ctx",
"index": "mdl_cont_conins_uix",
"field": "contextlevel",
"equals": "40",
"null_rejecting": false
},
{
"table": "ctx",
"index": "mdl_cont_conins_uix",
"field": "instanceid",
"equals": "cc.`id`",
"null_rejecting": false
},
{
"table": "ctx",
"index": "mdl_cont_ins_ix",
"field": "instanceid",
"equals": "cc.`id`",
"null_rejecting": false
},
{
"table": "ctx",
"index": "mdl_cont_ins_ix",
"field": "id",
"equals": "rc.contextid",
"null_rejecting": false
},
{
"table": "rc",
"index": "mdl_rolecapa_rolconcap_uix",
"field": "roleid",
"equals": "rc_ra.roleid",
"null_rejecting": false
},
{
"table": "rc",
"index": "mdl_rolecapa_rolconcap_uix",
"field": "contextid",
"equals": "ctx.`id`",
"null_rejecting": false
},
{
"table": "rc",
"index": "mdl_rolecapa_rol_ix",
"field": "roleid",
"equals": "rc_ra.roleid",
"null_rejecting": false
},
{
"table": "rc",
"index": "mdl_rolecapa_con_ix",
"field": "contextid",
"equals": "ctx.`id`",
"null_rejecting": false
},
{
"table": "rc_ra",
"index": "mdl_roleassi_rolcon_ix",
"field": "roleid",
"equals": "rc.roleid",
"null_rejecting": false
},
{
"table": "rc_ra",
"index": "mdl_roleassi_rolcon_ix",
"field": "contextid",
"equals": "rc_ra_ctx.`id`",
"null_rejecting": false
},
{
"table": "rc_ra",
"index": "mdl_roleassi_useconrol_ix",
"field": "userid",
"equals": "78244",
"null_rejecting": false
},
{
"table": "rc_ra",
"index": "mdl_roleassi_useconrol_ix",
"field": "contextid",
"equals": "rc_ra_ctx.`id`",
"null_rejecting": false
},
{
"table": "rc_ra",
"index": "mdl_roleassi_useconrol_ix",
"field": "roleid",
"equals": "rc.roleid",
"null_rejecting": false
},
{
"table": "rc_ra",
"index": "mdl_roleassi_rol_ix",
"field": "roleid",
"equals": "rc.roleid",
"null_rejecting": false
},
{
"table": "rc_ra",
"index": "mdl_roleassi_con_ix",
"field": "contextid",
"equals": "rc_ra_ctx.`id`",
"null_rejecting": false
},
{
"table": "rc_ra",
"index": "mdl_roleassi_use_ix",
"field": "userid",
"equals": "78244",
"null_rejecting": false
},
{
"table": "rc_ra_ctx",
"index": "PRIMARY",
"field": "id",
"equals": "rc_ra.contextid",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "cc",
"table_scan": {
"rows": 3737,
"cost": 97
}
},
{
"table": "ctx",
"range_analysis": {
"table_scan": {
"rows": 1328382,
"cost": 272253.4
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_cont_conins_uix",
"usable": true,
"key_parts": ["contextlevel", "instanceid"]
},
{
"index": "mdl_cont_ins_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_cont_pat_ix",
"usable": true,
"key_parts": ["path", "id"]
}
],
"setup_range_conditions": [],
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "mdl_cont_conins_uix",
"ranges": ["(40) <= (contextlevel) <= (40)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 3588,
"cost": 4311.796108,
"chosen": true
},
{
"index": "mdl_cont_pat_ix",
"ranges": [
"(/1/ ) <= (path) <= (/1/ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿ï¿¿)"
],
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 664191,
"cost": 860735.9769,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"cause": "too few roworder scans"
},
"analyzing_index_merge_union": []
},
"group_index_range": {
"chosen": false,
"cause": "not single_table"
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "mdl_cont_conins_uix",
"rows": 3588,
"ranges": ["(40) <= (contextlevel) <= (40)"]
},
"rows_for_plan": 3588,
"cost_for_plan": 4311.796108,
"chosen": true
}
}
},
{
"table": "ctx",
"rowid_filters": [
{
"key": "mdl_cont_conins_uix",
"build_cost": 317.8064737,
"rows": 3588
}
]
},
{
"selectivity_for_indexes": [
{
"index_name": "mdl_cont_conins_uix",
"selectivity_from_index": 0.00270103
},
{
"index_name": "mdl_cont_pat_ix",
"selectivity_from_index": 0.5
}
],
"selectivity_for_columns": [],
"cond_selectivity": 0.001350515
},
{
"table": "rc",
"table_scan": {
"rows": 34604,
"cost": 225
}
},
{
"table": "rc_ra",
"range_analysis": {
"table_scan": {
"rows": 3886566,
"cost": 799650.2
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_sor_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_rolcon_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_useconrol_ix",
"usable": true,
"key_parts": ["userid", "contextid", "roleid", "id"]
},
{
"index": "mdl_roleassi_comiteuse_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_rol_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_con_ix",
"usable": false,
"cause": "not applicable"
},
{
"index": "mdl_roleassi_use_ix",
"usable": true,
"key_parts": ["userid", "id"]
}
],
"best_covering_index_scan": {
"index": "mdl_roleassi_useconrol_ix",
"cost": 788926.2972,
"chosen": true
},
"setup_range_conditions": [],
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "mdl_roleassi_useconrol_ix",
"ranges": ["(78244) <= (userid) <= (78244)"],
"rowid_ordered": false,
"using_mrr": false,
"index_only": true,
"rows": 23,
"cost": 4.753983827,
"chosen": true
},
{
"index": "mdl_roleassi_use_ix",
"ranges": ["(78244) <= (userid) <= (78244)"],
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 23,
"cost": 27.74949191,
"chosen": false,
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"cause": "too few roworder scans"
},
"analyzing_index_merge_union": []
},
"group_index_range": {
"chosen": false,
"cause": "not single_table"
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "mdl_roleassi_useconrol_ix",
"rows": 23,
"ranges": ["(78244) <= (userid) <= (78244)"]
},
"rows_for_plan": 23,
"cost_for_plan": 4.753983827,
"chosen": true
}
}
},
{
"table": "rc_ra",
"rowid_filters": [
{
"key": "mdl_roleassi_use_ix",
"build_cost": 0.965655583,
"rows": 23
},
{
"key": "mdl_roleassi_use_ix",
"build_cost": 0.965655583,
"rows": 23
}
]
},
{
"selectivity_for_indexes": [
{
"index_name": "mdl_roleassi_useconrol_ix",
"selectivity_from_index": 5.917821e-6
}
],
"selectivity_for_columns": [],
"cond_selectivity": 5.917821e-6
},
{
"table": "rc_ra_ctx",
"table_scan": {
"rows": 1328382,
"cost": 6575
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "rc_ra",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_roleassi_useconrol_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 0.133983827,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_use_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 23.12949191,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 23,
"cost": 0.133983827,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "ctx",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_cont_conins_uix",
"used_range_estimates": true,
"rows": 3588,
"cost": 3594.176108,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 3588,
"cost": 3594.176108,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 3737,
"cost": 97,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 3737,
"cost": 97,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 34604,
"cost": 225,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 34604,
"cost": 225,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 1328382,
"cost": 6575,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 1328382,
"cost": 6575,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": [],
"table": "rc_ra",
"rows_for_plan": 23,
"cost_for_plan": 4.733983827,
"rest_of_plan": [
{
"plan_prefix": ["rc_ra"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "ctx",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_cont_conins_uix",
"used_range_estimates": true,
"rows": 3588,
"cost": 82666.05048,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 3588,
"cost": 82666.05048,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 3737,
"cost": 97,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 3737,
"cost": 97,
"uses_join_buffering": true
}
}
},
{
"best_access_path": {
"table": "rc",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_rolecapa_rolconcap_uix",
"rows": 1017,
"cost": 2253.651034,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_rolecapa_rol_ix",
"rows": 1017,
"cost": 15552.56828,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 1017,
"cost": 2253.651034,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 23,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 23,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["rc_ra"],
"table": "rc_ra_ctx",
"rows_for_plan": 23,
"cost_for_plan": 32.33398383,
"rest_of_plan": [
{
"plan_prefix": ["rc_ra", "rc_ra_ctx"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 3737,
"cost": 97,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 3737,
"cost": 97,
"uses_join_buffering": true
}
}
},
{
"best_access_path": {
"table": "ctx",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_cont_conins_uix",
"used_range_estimates": true,
"rows": 3588,
"cost": 82666.05048,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 3588,
"cost": 82666.05048,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_rolecapa_rolconcap_uix",
"rows": 1017,
"cost": 2253.651034,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_rolecapa_rol_ix",
"rows": 1017,
"cost": 15552.56828,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 1017,
"cost": 2253.651034,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["rc_ra", "rc_ra_ctx"],
"table": "rc",
"rows_for_plan": 23391,
"cost_for_plan": 6964.185018,
"rest_of_plan": [
{
"plan_prefix": ["rc_ra", "rc_ra_ctx", "rc"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 3737,
"cost": 97,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 3737,
"cost": 97,
"uses_join_buffering": true
}
}
},
{
"best_access_path": {
"table": "ctx",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 23391,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_cont_conins_uix",
"used_range_estimates": true,
"rows": 3588,
"cost": 84071373.34,
"chosen": false,
"cause": "cost"
},
{
"access_type": "ref",
"index": "mdl_cont_ins_ix",
"chosen": false,
"cause": "no predicate for first keypart"
},
{
"access_type": "range",
"resulting_rows": 1794,
"cost": 109249913.6,
"chosen": false
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 23391,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["rc_ra", "rc_ra_ctx", "rc"],
"table": "ctx",
"rows_for_plan": 23391,
"cost_for_plan": 35033.38502,
"selectivity": 0.001350515,
"estimated_join_cardinality": 31.58989959,
"rest_of_plan": [
{
"plan_prefix": ["rc_ra", "rc_ra_ctx", "rc", "ctx"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 31.58989959,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 31.58989959,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["rc_ra", "rc_ra_ctx", "rc", "ctx"],
"table": "cc",
"rows_for_plan": 31.58989959,
"cost_for_plan": 35071.2929
}
]
}
]
},
{
"plan_prefix": ["rc_ra", "rc_ra_ctx"],
"table": "ctx",
"rows_for_plan": 82524,
"cost_for_plan": 99203.18446,
"pruned_by_cost": true,
"current_cost": 99203.18446,
"best_cost": 35071.2929
},
{
"plan_prefix": ["rc_ra", "rc_ra_ctx"],
"table": "cc",
"rows_for_plan": 85951,
"cost_for_plan": 17319.53398,
"rest_of_plan": [
{
"plan_prefix": ["rc_ra", "rc_ra_ctx", "cc"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "rc",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_rolecapa_rolconcap_uix",
"rows": 1017,
"cost": 8421893.916,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_rolecapa_rol_ix",
"rows": 1017,
"cost": 58119947.65,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 1017,
"cost": 8421893.916,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "ctx",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "mdl_cont_conins_uix",
"rows": 1,
"cost": 3737,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_cont_ins_ix",
"rows": 1,
"cost": 171918.7863,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 3737,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["rc_ra", "rc_ra_ctx", "cc"],
"table": "ctx",
"rows_for_plan": 85951,
"cost_for_plan": 38246.73398,
"pruned_by_cost": true,
"current_cost": 38246.73398,
"best_cost": 35071.2929
},
{
"plan_prefix": ["rc_ra", "rc_ra_ctx", "cc"],
"table": "rc",
"rows_for_plan": 87412167,
"cost_for_plan": 25921646.85,
"pruned_by_cost": true,
"current_cost": 25921646.85,
"best_cost": 35071.2929
}
]
}
]
},
{
"plan_prefix": ["rc_ra"],
"table": "rc",
"rows_for_plan": 23391,
"cost_for_plan": 6936.585018,
"pruned_by_heuristic": true
},
{
"plan_prefix": ["rc_ra"],
"table": "ctx",
"rows_for_plan": 82524,
"cost_for_plan": 99175.58446,
"pruned_by_cost": true,
"current_cost": 99175.58446,
"best_cost": 35071.2929
},
{
"plan_prefix": ["rc_ra"],
"table": "cc",
"rows_for_plan": 85951,
"cost_for_plan": 17291.93398,
"pruned_by_heuristic": true
}
]
},
{
"plan_prefix": [],
"table": "ctx",
"rows_for_plan": 3588,
"cost_for_plan": 4311.776108,
"selectivity": 0.5,
"estimated_join_cardinality": 1794,
"rest_of_plan": [
{
"plan_prefix": ["ctx"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "rc_ra",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_roleassi_useconrol_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 240.366985,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_use_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 41494.30849,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 23,
"cost": 240.366985,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 1794,
"chosen": true
},
{
"access_type": "scan",
"resulting_rows": 3737,
"cost": 97,
"chosen": false
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 1794,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_rolecapa_rolconcap_uix",
"chosen": false,
"cause": "no predicate for first keypart"
},
{
"access_type": "ref",
"index": "mdl_rolecapa_con_ix",
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 1,
"cost": 3588.350369,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 1328382,
"cost": 6575,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 1328382,
"cost": 6575,
"uses_join_buffering": true
}
}
}
]
},
{
"plan_prefix": ["ctx"],
"table": "cc",
"rows_for_plan": 1794,
"cost_for_plan": 6464.576108,
"rest_of_plan": [
{
"plan_prefix": ["ctx", "cc"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 1328382,
"cost": 6575,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 1328382,
"cost": 6575,
"uses_join_buffering": true
}
}
},
{
"best_access_path": {
"table": "rc_ra",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_roleassi_useconrol_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 240.366985,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_use_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 41494.30849,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 23,
"cost": 240.366985,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_rolecapa_rolconcap_uix",
"chosen": false,
"cause": "no predicate for first keypart"
},
{
"access_type": "ref",
"index": "mdl_rolecapa_con_ix",
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 1,
"cost": 3588.350369,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["ctx", "cc"],
"table": "rc",
"rows_for_plan": 1794,
"cost_for_plan": 10411.72648,
"rest_of_plan": [
{
"plan_prefix": ["ctx", "cc", "rc"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "rc_ra",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_roleassi_rolcon_ix",
"rowid_filter_skipped": "worst/max seeks clipping",
"rows": 138805,
"cost": 120281713.5,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_useconrol_ix",
"rows": 9,
"cost": 1800.306646,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_rol_ix",
"rowid_filter_skipped": "worst/max seeks clipping",
"rows": 138805,
"cost": 120257397,
"chosen": false,
"cause": "cost"
},
{
"access_type": "ref",
"index": "mdl_roleassi_use_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 41494.30849,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 9,
"cost": 1800.306646,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 1328382,
"cost": 6575,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 1328382,
"cost": 6575,
"uses_join_buffering": true
}
}
}
]
},
{
"plan_prefix": ["ctx", "cc", "rc"],
"table": "rc_ra",
"rows_for_plan": 16146,
"cost_for_plan": 15441.23312,
"rest_of_plan": [
{
"plan_prefix": ["ctx", "cc", "rc", "rc_ra"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 16146,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 16146,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["ctx", "cc", "rc", "rc_ra"],
"table": "rc_ra_ctx",
"rows_for_plan": 16146,
"cost_for_plan": 34816.43312
}
]
},
{
"plan_prefix": ["ctx", "cc", "rc"],
"table": "rc_ra_ctx",
"rows_for_plan": 2383117308,
"cost_for_plan": 476640448.3,
"pruned_by_cost": true,
"current_cost": 476640448.3,
"best_cost": 34816.43312
}
]
},
{
"plan_prefix": ["ctx", "cc"],
"table": "rc_ra",
"rows_for_plan": 41262,
"cost_for_plan": 14957.34309,
"pruned_by_heuristic": true
},
{
"plan_prefix": ["ctx", "cc"],
"table": "rc_ra_ctx",
"rows_for_plan": 2383117308,
"cost_for_plan": 476636501.2,
"pruned_by_cost": true,
"current_cost": 476636501.2,
"best_cost": 34816.43312
}
]
},
{
"plan_prefix": ["ctx"],
"table": "rc",
"rows_for_plan": 1794,
"cost_for_plan": 8258.926477,
"pruned_by_heuristic": true
},
{
"plan_prefix": ["ctx"],
"table": "rc_ra",
"rows_for_plan": 41262,
"cost_for_plan": 12804.54309,
"pruned_by_heuristic": true
},
{
"plan_prefix": ["ctx"],
"table": "rc_ra_ctx",
"rows_for_plan": 2383117308,
"cost_for_plan": 476634348.4,
"pruned_by_cost": true,
"current_cost": 476634348.4,
"best_cost": 34816.43312
}
]
},
{
"plan_prefix": [],
"table": "cc",
"rows_for_plan": 3737,
"cost_for_plan": 844.4,
"rest_of_plan": [
{
"plan_prefix": ["cc"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "ctx",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "mdl_cont_conins_uix",
"rows": 1,
"cost": 3737,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_cont_ins_ix",
"rows": 1,
"cost": 7474.729838,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 3737,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc_ra",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_roleassi_useconrol_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 500.6975603,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_use_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 86434.91128,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 23,
"cost": 500.6975603,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 34604,
"cost": 225,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 34604,
"cost": 225,
"uses_join_buffering": true
}
}
},
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 1328382,
"cost": 6575,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 1328382,
"cost": 6575,
"uses_join_buffering": true
}
}
}
]
},
{
"plan_prefix": ["cc"],
"table": "ctx",
"rows_for_plan": 3737,
"cost_for_plan": 5328.8,
"selectivity": 0.5,
"estimated_join_cardinality": 1868.5,
"rest_of_plan": [
{
"plan_prefix": ["cc", "ctx"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 1328382,
"cost": 6575,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 1328382,
"cost": 6575,
"uses_join_buffering": true
}
}
},
{
"best_access_path": {
"table": "rc_ra",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_roleassi_useconrol_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 250.3487801,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_use_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 43217.45564,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 23,
"cost": 250.3487801,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_rolecapa_rolconcap_uix",
"chosen": false,
"cause": "no predicate for first keypart"
},
{
"access_type": "ref",
"index": "mdl_rolecapa_con_ix",
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 1,
"cost": 3737.364919,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["cc", "ctx"],
"table": "rc",
"rows_for_plan": 1868.5,
"cost_for_plan": 9439.864919,
"rest_of_plan": [
{
"plan_prefix": ["cc", "ctx", "rc"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "rc_ra",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_roleassi_rolcon_ix",
"rowid_filter_skipped": "worst/max seeks clipping",
"rows": 138805,
"cost": 125276689.9,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_useconrol_ix",
"rows": 9,
"cost": 1875.068544,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_rol_ix",
"rowid_filter_skipped": "worst/max seeks clipping",
"rows": 138805,
"cost": 125251363.6,
"chosen": false,
"cause": "cost"
},
{
"access_type": "ref",
"index": "mdl_roleassi_use_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 43217.45564,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 9,
"cost": 1875.068544,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 1328382,
"cost": 6575,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 1328382,
"cost": 6575,
"uses_join_buffering": true
}
}
}
]
},
{
"plan_prefix": ["cc", "ctx", "rc"],
"table": "rc_ra",
"rows_for_plan": 16816.5,
"cost_for_plan": 14678.23346,
"rest_of_plan": [
{
"plan_prefix": ["cc", "ctx", "rc", "rc_ra"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 16816.5,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 16816.5,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": ["cc", "ctx", "rc", "rc_ra"],
"table": "rc_ra_ctx",
"rows_for_plan": 16816.5,
"cost_for_plan": 34858.03346,
"pruned_by_cost": true,
"current_cost": 34858.03346,
"best_cost": 34816.43312
}
]
},
{
"plan_prefix": ["cc", "ctx", "rc"],
"table": "rc_ra_ctx",
"rows_for_plan": 2482081767,
"cost_for_plan": 496432368.3,
"pruned_by_cost": true,
"current_cost": 496432368.3,
"best_cost": 34816.43312
}
]
},
{
"plan_prefix": ["cc", "ctx"],
"table": "rc_ra",
"rows_for_plan": 42975.5,
"cost_for_plan": 14174.24878,
"pruned_by_heuristic": true
},
{
"plan_prefix": ["cc", "ctx"],
"table": "rc_ra_ctx",
"rows_for_plan": 2482081767,
"cost_for_plan": 496428257.2,
"pruned_by_cost": true,
"current_cost": 496428257.2,
"best_cost": 34816.43312
}
]
},
{
"plan_prefix": ["cc"],
"table": "rc_ra",
"rows_for_plan": 85951,
"cost_for_plan": 18535.29756,
"pruned_by_heuristic": true
},
{
"plan_prefix": ["cc"],
"table": "rc",
"rows_for_plan": 129315148,
"cost_for_plan": 25864099,
"pruned_by_cost": true,
"current_cost": 25864099,
"best_cost": 34816.43312
},
{
"plan_prefix": ["cc"],
"table": "rc_ra_ctx",
"rows_for_plan": 4964163534,
"cost_for_plan": 992840126.2,
"pruned_by_cost": true,
"current_cost": 992840126.2,
"best_cost": 34816.43312
}
]
},
{
"plan_prefix": [],
"table": "rc",
"rows_for_plan": 34604,
"cost_for_plan": 7145.8,
"rest_of_plan": [
{
"plan_prefix": ["rc"],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "ctx",
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 34604,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_cont_conins_uix",
"used_range_estimates": true,
"rows": 3588,
"cost": 124372870,
"chosen": false,
"cause": "cost"
},
{
"access_type": "ref",
"index": "mdl_cont_ins_ix",
"chosen": false,
"cause": "no predicate for first keypart"
},
{
"access_type": "range",
"resulting_rows": 1794,
"cost": 161621307.7,
"chosen": false
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 34604,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "cc",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 3737,
"cost": 97,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 3737,
"cost": 97,
"uses_join_buffering": true
}
}
},
{
"best_access_path": {
"table": "rc_ra",
"considered_access_paths": [
{
"access_type": "ref",
"index": "mdl_roleassi_rolcon_ix",
"rowid_filter_skipped": "worst/max seeks clipping",
"rows": 138805,
"cost": 2320082728,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_useconrol_ix",
"rows": 9,
"cost": 34725.64726,
"chosen": true
},
{
"access_type": "ref",
"index": "mdl_roleassi_rol_ix",
"rowid_filter_skipped": "worst/max seeks clipping",
"rows": 138805,
"cost": 2319613693,
"chosen": false,
"cause": "cost"
},
{
"access_type": "ref",
"index": "mdl_roleassi_use_ix",
"used_range_estimates": true,
"rows": 23,
"cost": 800372.9382,
"chosen": false,
"cause": "cost"
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "ref",
"records": 9,
"cost": 34725.64726,
"uses_join_buffering": false
}
}
},
{
"best_access_path": {
"table": "rc_ra_ctx",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 1328382,
"cost": 6575,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 1328382,
"cost": 6575,
"uses_join_buffering": true
}
}
}
]
},
{
"plan_prefix": ["rc"],
"table": "ctx",
"rows_for_plan": 34604,
"cost_for_plan": 48670.6,
"pruned_by_cost": true,
"current_cost": 48670.6,
"best_cost": 34816.43312
},
{
"plan_prefix": ["rc"],
"table": "rc_ra",
"rows_for_plan": 311436,
"cost_for_plan": 104158.6473,
"pruned_by_cost": true,
"current_cost": 104158.6473,
"best_cost": 34816.43312
},
{
"plan_prefix": ["rc"],
"table": "cc",
"rows_for_plan": 129315148,
"cost_for_plan": 25870272.4,
"pruned_by_cost": true,
"current_cost": 25870272.4,
"best_cost": 34816.43312
},
{
"plan_prefix": ["rc"],
"table": "rc_ra_ctx",
"rows_for_plan": 45967330728,
"cost_for_plan": 9193479866,
"pruned_by_cost": true,
"current_cost": 9193479866,
"best_cost": 34816.43312
}
]
},
{
"plan_prefix": [],
"table": "rc_ra_ctx",
"rows_for_plan": 1328382,
"cost_for_plan": 272251.4,
"pruned_by_cost": true,
"current_cost": 272251.4,
"best_cost": 34816.43312
}
]
},
{
"best_join_order": ["ctx", "cc", "rc", "rc_ra", "rc_ra_ctx"]
},
{
"substitute_best_equal": {
"condition": "WHERE",
"resulting_condition": "rc_ra.userid = 78244 and rc_ra_ctx.`id` = rc_ra.contextid and rc_ra.roleid = rc.roleid and rc.contextid = ctx.`id` and cc.`id` = ctx.instanceid and ctx.contextlevel = 40 and ctx.`path` like '/1/%' and (rc_ra_ctx.`path` = ctx.`path` or ctx.`path` like concat(rc_ra_ctx.`path`,'/%'))"
}
},
{
"attaching_conditions_to_tables": {
"attached_conditions_computation": [],
"attached_conditions_summary": [
{
"table": "ctx",
"attached": "ctx.`path` like '/1/%'"
},
{
"table": "cc",
"attached": null
},
{
"table": "rc",
"attached": null
},
{
"table": "rc_ra",
"attached": "rc_ra.roleid = rc.roleid"
},
{
"table": "rc_ra_ctx",
"attached": "rc_ra_ctx.`path` = ctx.`path` or ctx.`path` like concat(rc_ra_ctx.`path`,'/%')"
}
]
}
}
]
}
},
{
"join_execution": {
"select_id": 1,
"steps": []
}
},
{
"join_execution": {
"select_id": 2,
"steps": []
}
},
{
"join_preparation": {
"select_id": "fake",
"steps": [
{
"expanded_query": "select `id` AS `id`,`name` AS `name`,idnumber AS idnumber,parent AS parent,sortorder AS sortorder,coursecount AS coursecount,`visible` AS `visible`,depth AS depth,`path` AS `path`,ctxid AS ctxid,ctxpath AS ctxpath,ctxdepth AS ctxdepth,ctxlevel AS ctxlevel,ctxinstance AS ctxinstance,ctxlocked AS ctxlocked from dual"
}
]
}
},
{
"join_optimization": {
"select_id": "fake",
"steps": [
{
"table_dependencies": [
{
"table": "union",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
}
]
},
{
"rows_estimation": [
{
"table": "union",
"table_scan": {
"rows": 3,
"cost": 10.15
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [],
"get_costs_for_tables": [
{
"best_access_path": {
"table": "union",
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 3,
"cost": 10.15,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 3,
"cost": 10.15,
"uses_join_buffering": false
}
}
}
]
},
{
"plan_prefix": [],
"table": "union",
"rows_for_plan": 3,
"cost_for_plan": 10.75
}
]
},
{
"best_join_order": ["union"]
},
{
"attaching_conditions_to_tables": {
"attached_conditions_computation": [],
"attached_conditions_summary": [
{
"table": "union",
"attached": null
}
]
}
}
]
}
},
{
"join_execution": {
"select_id": "fake",
"steps": []
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0,002 sec)