Event Timeline
Comment Actions
The optimizer trace reveals that for some reason, it believes it is faster to do a full scan even though if the cost is higher :(
Do you mind sending a bug to MariaDB about this? This is the optimizer trace:
root@db1133.eqiad.wmnet[mediabackups]> select * from information_schema.optimizer_trace limit 1\G
*************************** 1. row ***************************
QUERY: select wiki_name, upload_name, size, status_name, sha1, backup_status_name FROM files JOIN file_status ON file_status.id = files.status JOIN backup_status ON backup_status.id = files.backup_status JOIN wikis ON wikis.id = files.wiki ORDER BY files.id LIMIT 10
TRACE: {
"steps": [
{
"join_preparation": {
"select_id": 1,
"steps": [
{
"expanded_query": "select wikis.wiki_name AS wiki_name,files.upload_name AS upload_name,files.size AS size,file_status.status_name AS status_name,files.sha1 AS sha1,backup_status.backup_status_name AS backup_status_name from (((files join file_status on(file_status.`id` = files.`status`)) join backup_status on(backup_status.`id` = files.backup_status)) join wikis on(wikis.`id` = files.wiki)) order by files.`id` limit 10"
}
]
}
},
{
"join_optimization": {
"select_id": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "wikis.`id` = files.wiki and backup_status.`id` = files.backup_status and file_status.`id` = files.`status`",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "multiple equal(wikis.`id`, files.wiki) and multiple equal(backup_status.`id`, files.backup_status) and multiple equal(file_status.`id`, files.`status`)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal(wikis.`id`, files.wiki) and multiple equal(backup_status.`id`, files.backup_status) and multiple equal(file_status.`id`, files.`status`)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(wikis.`id`, files.wiki) and multiple equal(backup_status.`id`, files.backup_status) and multiple equal(file_status.`id`, files.`status`)"
}
]
}
},
{
"table_dependencies": [
{
"table": "files",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
},
{
"table": "file_status",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": []
},
{
"table": "backup_status",
"row_may_be_null": false,
"map_bit": 2,
"depends_on_map_bits": []
},
{
"table": "wikis",
"row_may_be_null": false,
"map_bit": 3,
"depends_on_map_bits": []
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "files",
"field": "status",
"equals": "file_status.`id`",
"null_rejecting": true
},
{
"table": "files",
"field": "backup_status",
"equals": "backup_status.`id`",
"null_rejecting": true
},
{
"table": "files",
"field": "wiki",
"equals": "wikis.`id`",
"null_rejecting": false
},
{
"table": "files",
"field": "status",
"equals": "file_status.`id`",
"null_rejecting": true
},
{
"table": "files",
"field": "backup_status",
"equals": "backup_status.`id`",
"null_rejecting": true
},
{
"table": "files",
"field": "wiki",
"equals": "wikis.`id`",
"null_rejecting": false
},
{
"table": "file_status",
"field": "id",
"equals": "files.`status`",
"null_rejecting": true
},
{
"table": "file_status",
"field": "id",
"equals": "files.`status`",
"null_rejecting": true
},
{
"table": "backup_status",
"field": "id",
"equals": "files.backup_status",
"null_rejecting": true
},
{
"table": "backup_status",
"field": "id",
"equals": "files.backup_status",
"null_rejecting": true
},
{
"table": "wikis",
"field": "id",
"equals": "files.wiki",
"null_rejecting": false
},
{
"table": "wikis",
"field": "id",
"equals": "files.wiki",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "files",
"table_scan": {
"rows": 4549851,
"cost": 46144
}
},
{
"table": "file_status",
"table_scan": {
"rows": 4,
"cost": 1
}
},
{
"table": "backup_status",
"table_scan": {
"rows": 4,
"cost": 1
}
},
{
"table": "wikis",
"table_scan": {
"rows": 999,
"cost": 4
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [],
"table": "file_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 1,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4,
"cost": 1,
"uses_join_buffering": false
}
},
"rows_for_plan": 4,
"cost_for_plan": 1.8,
"rest_of_plan": [
{
"plan_prefix": ["file_status"],
"table": "backup_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 1,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4,
"cost": 1,
"uses_join_buffering": true
}
},
"rows_for_plan": 16,
"cost_for_plan": 6,
"rest_of_plan": [
{
"plan_prefix": ["file_status", "backup_status"],
"table": "wikis",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 999,
"cost": 4,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 999,
"cost": 4,
"uses_join_buffering": true
}
},
"rows_for_plan": 15984,
"cost_for_plan": 3206.8,
"rest_of_plan": [
{
"plan_prefix": [
"file_status",
"backup_status",
"wikis"
],
"table": "files",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "status",
"rows": 1.52e6,
"cost": 2.21e9,
"chosen": true
},
{
"access_type": "ref",
"index": "backup_status",
"rows": 2.27e6,
"cost": 2.21e9,
"chosen": false,
"cause": "cost"
},
{
"access_type": "ref",
"index": "wiki",
"rows": 4.55e6,
"cost": 2.21e9,
"chosen": false,
"cause": "cost"
},
{
"access_type": "scan",
"resulting_rows": 4.55e6,
"cost": 784448,
"chosen": false
}
],
"chosen_access_method": {
"type": "ref",
"records": 1.52e6,
"cost": 2.21e9,
"uses_join_buffering": false
}
},
"rows_for_plan": 2.4e10,
"cost_for_plan": 7.06e9,
"cost_for_sorting": 2.4e10,
"estimated_join_cardinality": 2.4e10
}
]
},
{
"plan_prefix": ["file_status", "backup_status"],
"table": "files",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "status",
"rows": 1.52e6,
"cost": 2.21e6,
"chosen": true
},
{
"access_type": "ref",
"index": "backup_status",
"rows": 2.27e6,
"cost": 2.21e6,
"chosen": false,
"cause": "cost"
},
{
"access_type": "scan",
"resulting_rows": 4.55e6,
"cost": 46144,
"chosen": false
}
],
"chosen_access_method": {
"type": "ref",
"records": 1.52e6,
"cost": 2.21e6,
"uses_join_buffering": false
}
},
"rows_for_plan": 2.43e7,
"cost_for_plan": 7.07e6,
"rest_of_plan": [
{
"plan_prefix": [
"file_status",
"backup_status",
"files"
],
"table": "wikis",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 6.07e6,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 6.07e6,
"uses_join_buffering": false
}
},
"rows_for_plan": 2.43e7,
"cost_for_plan": 1.8e7,
"cost_for_sorting": 2.43e7,
"estimated_join_cardinality": 2.43e7
}
]
}
]
},
{
"plan_prefix": ["file_status"],
"table": "wikis",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 999,
"cost": 4,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 999,
"cost": 4,
"uses_join_buffering": true
}
},
"rows_for_plan": 3996,
"cost_for_plan": 805,
"rest_of_plan": [
{
"plan_prefix": ["file_status", "wikis"],
"table": "backup_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 3,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4,
"cost": 3,
"uses_join_buffering": true
}
},
"rows_for_plan": 15984,
"cost_for_plan": 4004.8,
"rest_of_plan": [
{
"plan_prefix": [
"file_status",
"wikis",
"backup_status"
],
"table": "files",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "status",
"rows": 1.52e6,
"cost": 2.21e9,
"chosen": true
},
{
"access_type": "ref",
"index": "backup_status",
"rows": 2.27e6,
"cost": 2.21e9,
"chosen": false,
"cause": "cost"
},
{
"access_type": "ref",
"index": "wiki",
"rows": 4.55e6,
"cost": 2.21e9,
"chosen": false,
"cause": "cost"
},
{
"access_type": "scan",
"resulting_rows": 4.55e6,
"cost": 784448,
"chosen": false
}
],
"chosen_access_method": {
"type": "ref",
"records": 1.52e6,
"cost": 2.21e9,
"uses_join_buffering": false
}
},
"rows_for_plan": 2.4e10,
"cost_for_plan": 7.06e9,
"pruned_by_cost": true
}
]
},
{
"plan_prefix": ["file_status", "wikis"],
"table": "files",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "status",
"rows": 1.52e6,
"cost": 5.53e8,
"chosen": true
},
{
"access_type": "ref",
"index": "wiki",
"rows": 4.55e6,
"cost": 5.53e8,
"chosen": false,
"cause": "cost"
},
{
"access_type": "scan",
"resulting_rows": 4.55e6,
"cost": 138432,
"chosen": false
}
],
"chosen_access_method": {
"type": "ref",
"records": 1.52e6,
"cost": 5.53e8,
"uses_join_buffering": false
}
},
"rows_for_plan": 6.06e9,
"cost_for_plan": 1.77e9,
"pruned_by_cost": true
}
]
},
{
"plan_prefix": ["file_status"],
"table": "files",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "status",
"rows": 1.52e6,
"cost": 553732,
"chosen": true
},
{
"access_type": "scan",
"resulting_rows": 4.55e6,
"cost": 46144,
"chosen": false
}
],
"chosen_access_method": {
"type": "ref",
"records": 1.52e6,
"cost": 553732,
"uses_join_buffering": false
}
},
"rows_for_plan": 6.07e6,
"cost_for_plan": 1.77e6,
"rest_of_plan": [
{
"plan_prefix": ["file_status", "files"],
"table": "wikis",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 6.07e6,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 6.07e6,
"uses_join_buffering": false
}
},
"rows_for_plan": 6.07e6,
"cost_for_plan": 9.05e6,
"rest_of_plan": [
{
"plan_prefix": ["file_status", "files", "wikis"],
"table": "backup_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 6.07e6,
"chosen": true
},
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 7892,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4,
"cost": 7892,
"uses_join_buffering": true
}
},
"rows_for_plan": 2.43e7,
"cost_for_plan": 1.39e7,
"cost_for_sorting": 2.43e7,
"estimated_join_cardinality": 2.43e7
}
]
},
{
"plan_prefix": ["file_status", "files"],
"table": "backup_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 6.07e6,
"chosen": true
},
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 6388,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4,
"cost": 6388,
"uses_join_buffering": true
}
},
"rows_for_plan": 2.43e7,
"cost_for_plan": 6.63e6,
"rest_of_plan": [
{
"plan_prefix": [
"file_status",
"files",
"backup_status"
],
"table": "wikis",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 6.07e6,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 6.07e6,
"uses_join_buffering": false
}
},
"rows_for_plan": 2.43e7,
"cost_for_plan": 1.75e7,
"cost_for_sorting": 2.43e7,
"estimated_join_cardinality": 2.43e7
}
]
}
]
}
]
},
{
"plan_prefix": [],
"table": "backup_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 1,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4,
"cost": 1,
"uses_join_buffering": false
}
},
"rows_for_plan": 4,
"cost_for_plan": 1.8,
"rest_of_plan": [
{
"plan_prefix": ["backup_status"],
"table": "file_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 1,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4,
"cost": 1,
"uses_join_buffering": true
}
},
"rows_for_plan": 16,
"cost_for_plan": 6,
"rest_of_plan": [
{
"plan_prefix": ["backup_status", "file_status"],
"table": "wikis",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 999,
"cost": 4,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 999,
"cost": 4,
"uses_join_buffering": true
}
},
"rows_for_plan": 15984,
"cost_for_plan": 3206.8,
"rest_of_plan": [
{
"plan_prefix": [
"backup_status",
"file_status",
"wikis"
],
"table": "files",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "status",
"rows": 1.52e6,
"cost": 2.21e9,
"chosen": true
},
{
"access_type": "ref",
"index": "backup_status",
"rows": 2.27e6,
"cost": 2.21e9,
"chosen": false,
"cause": "cost"
},
{
"access_type": "ref",
"index": "wiki",
"rows": 4.55e6,
"cost": 2.21e9,
"chosen": false,
"cause": "cost"
},
{
"access_type": "scan",
"resulting_rows": 4.55e6,
"cost": 784448,
"chosen": false
}
],
"chosen_access_method": {
"type": "ref",
"records": 1.52e6,
"cost": 2.21e9,
"uses_join_buffering": false
}
},
"rows_for_plan": 2.4e10,
"cost_for_plan": 7.06e9,
"pruned_by_cost": true
}
]
},
{
"plan_prefix": ["backup_status", "file_status"],
"table": "files",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "status",
"rows": 1.52e6,
"cost": 2.21e6,
"chosen": true
},
{
"access_type": "ref",
"index": "backup_status",
"rows": 2.27e6,
"cost": 2.21e6,
"chosen": false,
"cause": "cost"
},
{
"access_type": "scan",
"resulting_rows": 4.55e6,
"cost": 46144,
"chosen": false
}
],
"chosen_access_method": {
"type": "ref",
"records": 1.52e6,
"cost": 2.21e6,
"uses_join_buffering": false
}
},
"rows_for_plan": 2.43e7,
"cost_for_plan": 7.07e6,
"rest_of_plan": [
{
"plan_prefix": [
"backup_status",
"file_status",
"files"
],
"table": "wikis",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 6.07e6,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 6.07e6,
"uses_join_buffering": false
}
},
"rows_for_plan": 2.43e7,
"cost_for_plan": 1.8e7,
"cost_for_sorting": 2.43e7,
"estimated_join_cardinality": 2.43e7
}
]
}
]
},
{
"plan_prefix": ["backup_status"],
"table": "wikis",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 999,
"cost": 4,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 999,
"cost": 4,
"uses_join_buffering": true
}
},
"rows_for_plan": 3996,
"cost_for_plan": 805,
"rest_of_plan": [
{
"plan_prefix": ["backup_status", "wikis"],
"table": "file_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 3,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4,
"cost": 3,
"uses_join_buffering": true
}
},
"rows_for_plan": 15984,
"cost_for_plan": 4004.8,
"rest_of_plan": [
{
"plan_prefix": [
"backup_status",
"wikis",
"file_status"
],
"table": "files",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "status",
"rows": 1.52e6,
"cost": 2.21e9,
"chosen": true
},
{
"access_type": "ref",
"index": "backup_status",
"rows": 2.27e6,
"cost": 2.21e9,
"chosen": false,
"cause": "cost"
},
{
"access_type": "ref",
"index": "wiki",
"rows": 4.55e6,
"cost": 2.21e9,
"chosen": false,
"cause": "cost"
},
{
"access_type": "scan",
"resulting_rows": 4.55e6,
"cost": 784448,
"chosen": false
}
],
"chosen_access_method": {
"type": "ref",
"records": 1.52e6,
"cost": 2.21e9,
"uses_join_buffering": false
}
},
"rows_for_plan": 2.4e10,
"cost_for_plan": 7.06e9,
"pruned_by_cost": true
}
]
},
{
"plan_prefix": ["backup_status", "wikis"],
"table": "files",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "backup_status",
"rows": 2.27e6,
"cost": 5.53e8,
"chosen": true
},
{
"access_type": "ref",
"index": "wiki",
"rows": 4.55e6,
"cost": 5.53e8,
"chosen": false,
"cause": "cost"
},
{
"access_type": "scan",
"resulting_rows": 4.55e6,
"cost": 138432,
"chosen": false
}
],
"chosen_access_method": {
"type": "ref",
"records": 2.27e6,
"cost": 5.53e8,
"uses_join_buffering": false
}
},
"rows_for_plan": 9.09e9,
"cost_for_plan": 2.37e9,
"pruned_by_cost": true
}
]
},
{
"plan_prefix": ["backup_status"],
"table": "files",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "backup_status",
"rows": 2.27e6,
"cost": 553732,
"chosen": true
},
{
"access_type": "scan",
"resulting_rows": 4.55e6,
"cost": 46144,
"chosen": false
}
],
"chosen_access_method": {
"type": "ref",
"records": 2.27e6,
"cost": 553732,
"uses_join_buffering": false
}
},
"rows_for_plan": 9.1e6,
"cost_for_plan": 2.37e6,
"rest_of_plan": [
{
"plan_prefix": ["backup_status", "files"],
"table": "wikis",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 9.1e6,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 9.1e6,
"uses_join_buffering": false
}
},
"rows_for_plan": 9.1e6,
"cost_for_plan": 1.33e7,
"rest_of_plan": [
{
"plan_prefix": ["backup_status", "files", "wikis"],
"table": "file_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 9.1e6,
"chosen": true
},
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 11837,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4,
"cost": 11837,
"uses_join_buffering": true
}
},
"rows_for_plan": 3.64e7,
"cost_for_plan": 2.06e7,
"cost_for_sorting": 3.64e7,
"estimated_join_cardinality": 3.64e7
}
]
},
{
"plan_prefix": ["backup_status", "files"],
"table": "file_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 9.1e6,
"chosen": true
},
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 9581,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4,
"cost": 9581,
"uses_join_buffering": true
}
},
"rows_for_plan": 3.64e7,
"cost_for_plan": 9.66e6,
"rest_of_plan": [
{
"plan_prefix": [
"backup_status",
"files",
"file_status"
],
"table": "wikis",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 9.1e6,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 9.1e6,
"uses_join_buffering": false
}
},
"rows_for_plan": 3.64e7,
"cost_for_plan": 2.6e7,
"cost_for_sorting": 3.64e7,
"estimated_join_cardinality": 3.64e7
}
]
}
]
}
]
},
{
"plan_prefix": [],
"table": "wikis",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 999,
"cost": 4,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 999,
"cost": 4,
"uses_join_buffering": false
}
},
"rows_for_plan": 999,
"cost_for_plan": 203.8,
"rest_of_plan": [
{
"plan_prefix": ["wikis"],
"table": "backup_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 1,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4,
"cost": 1,
"uses_join_buffering": true
}
},
"rows_for_plan": 3996,
"cost_for_plan": 1004,
"rest_of_plan": [
{
"plan_prefix": ["wikis", "backup_status"],
"table": "file_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 3,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4,
"cost": 3,
"uses_join_buffering": true
}
},
"rows_for_plan": 15984,
"cost_for_plan": 4203.8,
"rest_of_plan": [
{
"plan_prefix": [
"wikis",
"backup_status",
"file_status"
],
"table": "files",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "status",
"rows": 1.52e6,
"cost": 2.21e9,
"chosen": true
},
{
"access_type": "ref",
"index": "backup_status",
"rows": 2.27e6,
"cost": 2.21e9,
"chosen": false,
"cause": "cost"
},
{
"access_type": "ref",
"index": "wiki",
"rows": 4.55e6,
"cost": 2.21e9,
"chosen": false,
"cause": "cost"
},
{
"access_type": "scan",
"resulting_rows": 4.55e6,
"cost": 784448,
"chosen": false
}
],
"chosen_access_method": {
"type": "ref",
"records": 1.52e6,
"cost": 2.21e9,
"uses_join_buffering": false
}
},
"rows_for_plan": 2.4e10,
"cost_for_plan": 7.06e9,
"pruned_by_cost": true
}
]
},
{
"plan_prefix": ["wikis", "backup_status"],
"table": "files",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "backup_status",
"rows": 2.27e6,
"cost": 5.53e8,
"chosen": true
},
{
"access_type": "ref",
"index": "wiki",
"rows": 4.55e6,
"cost": 5.53e8,
"chosen": false,
"cause": "cost"
},
{
"access_type": "scan",
"resulting_rows": 4.55e6,
"cost": 138432,
"chosen": false
}
],
"chosen_access_method": {
"type": "ref",
"records": 2.27e6,
"cost": 5.53e8,
"uses_join_buffering": false
}
},
"rows_for_plan": 9.09e9,
"cost_for_plan": 2.37e9,
"pruned_by_cost": true
}
]
},
{
"plan_prefix": ["wikis"],
"table": "file_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 1,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4,
"cost": 1,
"uses_join_buffering": true
}
},
"rows_for_plan": 3996,
"cost_for_plan": 1004,
"rest_of_plan": [
{
"plan_prefix": ["wikis", "file_status"],
"table": "backup_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 3,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4,
"cost": 3,
"uses_join_buffering": true
}
},
"rows_for_plan": 15984,
"cost_for_plan": 4203.8,
"rest_of_plan": [
{
"plan_prefix": [
"wikis",
"file_status",
"backup_status"
],
"table": "files",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "status",
"rows": 1.52e6,
"cost": 2.21e9,
"chosen": true
},
{
"access_type": "ref",
"index": "backup_status",
"rows": 2.27e6,
"cost": 2.21e9,
"chosen": false,
"cause": "cost"
},
{
"access_type": "ref",
"index": "wiki",
"rows": 4.55e6,
"cost": 2.21e9,
"chosen": false,
"cause": "cost"
},
{
"access_type": "scan",
"resulting_rows": 4.55e6,
"cost": 784448,
"chosen": false
}
],
"chosen_access_method": {
"type": "ref",
"records": 1.52e6,
"cost": 2.21e9,
"uses_join_buffering": false
}
},
"rows_for_plan": 2.4e10,
"cost_for_plan": 7.06e9,
"pruned_by_cost": true
}
]
},
{
"plan_prefix": ["wikis", "file_status"],
"table": "files",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "status",
"rows": 1.52e6,
"cost": 5.53e8,
"chosen": true
},
{
"access_type": "ref",
"index": "wiki",
"rows": 4.55e6,
"cost": 5.53e8,
"chosen": false,
"cause": "cost"
},
{
"access_type": "scan",
"resulting_rows": 4.55e6,
"cost": 138432,
"chosen": false
}
],
"chosen_access_method": {
"type": "ref",
"records": 1.52e6,
"cost": 5.53e8,
"uses_join_buffering": false
}
},
"rows_for_plan": 6.06e9,
"cost_for_plan": 1.77e9,
"pruned_by_cost": true
}
]
},
{
"plan_prefix": ["wikis"],
"table": "files",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "wiki",
"rows": 4.55e6,
"cost": 1.38e8,
"chosen": true
},
{
"access_type": "scan",
"resulting_rows": 4.55e6,
"cost": 46144,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4.55e6,
"cost": 46144,
"uses_join_buffering": true
}
},
"rows_for_plan": 4.55e9,
"cost_for_plan": 9.09e8,
"pruned_by_cost": true
}
]
},
{
"plan_prefix": [],
"table": "files",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"resulting_rows": 4.55e6,
"cost": 46144,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4.55e6,
"cost": 46144,
"uses_join_buffering": false
}
},
"rows_for_plan": 4.55e6,
"cost_for_plan": 956114,
"rest_of_plan": [
{
"plan_prefix": ["files"],
"table": "backup_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 4.55e6,
"chosen": true
},
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 3003,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4,
"cost": 3003,
"uses_join_buffering": true
}
},
"rows_for_plan": 1.82e7,
"cost_for_plan": 4.6e6,
"rest_of_plan": [
{
"plan_prefix": ["files", "backup_status"],
"table": "wikis",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 4.55e6,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 4.55e6,
"uses_join_buffering": false
}
},
"rows_for_plan": 1.82e7,
"cost_for_plan": 1.28e7,
"rest_of_plan": [
{
"plan_prefix": ["files", "backup_status", "wikis"],
"table": "file_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 4.55e6,
"chosen": true
},
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 23674,
"chosen": false
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 4.55e6,
"uses_join_buffering": false
}
},
"rows_for_plan": 1.82e7,
"cost_for_plan": 2.1e7,
"estimated_join_cardinality": 1.82e7
}
]
},
{
"plan_prefix": ["files", "backup_status"],
"table": "file_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 4.55e6,
"chosen": true
},
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 19162,
"chosen": false
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 4.55e6,
"uses_join_buffering": false
}
},
"rows_for_plan": 1.82e7,
"cost_for_plan": 1.28e7,
"pruned_by_heuristic": true
}
]
},
{
"plan_prefix": ["files"],
"table": "wikis",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 4.55e6,
"chosen": true
},
{
"type": "scan",
"chosen": false,
"cause": "cost"
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 4.55e6,
"uses_join_buffering": false
}
},
"rows_for_plan": 4.55e6,
"cost_for_plan": 6.42e6,
"rest_of_plan": [
{
"plan_prefix": ["files", "wikis"],
"table": "backup_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 4.55e6,
"chosen": true
},
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 4131,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4,
"cost": 4131,
"uses_join_buffering": true
}
},
"rows_for_plan": 1.82e7,
"cost_for_plan": 1.01e7,
"rest_of_plan": [
{
"plan_prefix": ["files", "wikis", "backup_status"],
"table": "file_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 4.55e6,
"chosen": true
},
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 23674,
"chosen": false
}
],
"chosen_access_method": {
"type": "eq_ref",
"records": 1,
"cost": 4.55e6,
"uses_join_buffering": false
}
},
"rows_for_plan": 1.82e7,
"cost_for_plan": 1.82e7,
"estimated_join_cardinality": 1.82e7
}
]
},
{
"plan_prefix": ["files", "wikis"],
"table": "file_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 4.55e6,
"chosen": true
},
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 4131,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4,
"cost": 4131,
"uses_join_buffering": true
}
},
"rows_for_plan": 1.82e7,
"cost_for_plan": 1.01e7,
"pruned_by_heuristic": true
}
]
},
{
"plan_prefix": ["files"],
"table": "file_status",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "eq_ref",
"index": "PRIMARY",
"rows": 1,
"cost": 4.55e6,
"chosen": true
},
{
"access_type": "scan",
"resulting_rows": 4,
"cost": 3003,
"chosen": true
}
],
"chosen_access_method": {
"type": "scan",
"records": 4,
"cost": 3003,
"uses_join_buffering": true
}
},
"rows_for_plan": 1.82e7,
"cost_for_plan": 4.6e6,
"pruned_by_heuristic": true
}
]
}
]
},
{
"best_join_order": ["files", "wikis", "backup_status", "file_status"]
},
{
"attaching_conditions_to_tables": {
"original_condition": "wikis.`id` = files.wiki and backup_status.`id` = files.backup_status and file_status.`id` = files.`status`",
"attached_conditions_computation": [
{
"table": "backup_status",
"range_analysis": {
"table_scan": {
"rows": 4,
"cost": 3.9
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": ["id"]
}
],
"setup_range_conditions": [],
"group_index_range": {
"chosen": false,
"cause": "not single_table"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "PRIMARY",
"chosen": false,
"cause": "depends on unread values"
}
],
"analyzing_roworder_intersect": {
"cause": "too few roworder scans"
},
"analyzing_index_merge_union": []
}
}
}
],
"attached_conditions_summary": [
{
"table": "files",
"attached": "files.`status` is not null"
},
{
"table": "wikis",
"attached": null
},
{
"table": "backup_status",
"attached": "backup_status.`id` = files.backup_status"
},
{
"table": "file_status",
"attached": null
}
]
}
}
]
}
},
{
"join_execution": {
"select_id": 1,
"steps": []
}
}
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0Comment Actions
root@db2102.codfw.wmnet[mediabackups]> explain select wiki_name, upload_name, size, status_name, sha1, backup_status_name FROM files JOIN file_status ON file_status.id = files.status JOIN backup_status ON backup_status.id = files.backup_status JOIN wikis ON wikis.id = files.wiki ORDER BY files.id LIMIT 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: files
partitions: NULL
type: index
possible_keys: status,backup_status,wiki
key: PRIMARY <------------------------------------- preferred index
key_len: 4
ref: NULL
rows: 10
filtered: 100.00
Extra: Using where <------------------------------------ no filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: file_status
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 1
ref: mediabackups.files.status
rows: 1
filtered: 100.00
Extra: NULL
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: backup_status
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 1
ref: mediabackups.files.backup_status
rows: 1
filtered: 100.00
Extra: NULL
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: wikis
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: mediabackups.files.wiki
rows: 1
filtered: 100.00
Extra: NULL
4 rows in set, 1 warning (0.035 sec)
root@db2102.codfw.wmnet[mediabackups]> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `mediabackups`.`wikis`.`wiki_name` AS `wiki_name`,`mediabackups`.`files`.`upload_name` AS `upload_name`,`mediabackups`.`files`.`size` AS `size`,`mediabackups`.`file_status`.`status_name` AS `status_name`,`mediabackups`.`files`.`sha1` AS `sha1`,`mediabackups`.`backup_status`.`backup_status_name` AS `backup_status_name` from `mediabackups`.`files` join `mediabackups`.`file_status` join `mediabackups`.`backup_status` join `mediabackups`.`wikis` where ((`mediabackups`.`file_status`.`id` = `mediabackups`.`files`.`status`) and (`mediabackups`.`backup_status`.`id` = `mediabackups`.`files`.`backup_status`) and (`mediabackups`.`wikis`.`id` = `mediabackups`.`files`.`wiki`)) order by `mediabackups`.`files`.`id` limit 10 |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.034 sec)
root@db2102.codfw.wmnet[mediabackups]> select wiki_name, upload_name, size, status_name, sha1, backup_status_name FROM files JOIN file_status ON file_status.id = files.status JOIN backup_status ON backup_status.id = files.backup_status JOIN wikis ON wikis.id = files.wiki ORDER BY files.id LIMIT 10;
+-----------+---------------------------------------------------------+--------+-------------+------------------------------------------+--------------------+
| wiki_name | upload_name | size | status_name | sha1 | backup_status_name |
+-----------+---------------------------------------------------------+--------+-------------+------------------------------------------+--------------------+
| enwiki | !!!_(Chk_Chk_Chk)_-_One_Girl_One_Boy_cover_art.jpg | 31850 | public | 2c5f4c5ff0e57ffcea85c1da92b4599336d75fb9 | backedup |
| enwiki | !!!_-_!!!_album_cover.jpg | 43672 | public | 25c046a856d14314cda3c741539f41fbc6c63fe2 | backedup |
| enwiki | !!!_-_Wallop.png | 118745 | public | 3fccf40bfd27ccfac1d057198fd4315afd50b42d | backedup |
| enwiki | !0_Trombones_Like_2_Pianos.jpg | 25319 | public | 3ec8b40e128ad8c677b869ef9280104c03bff2e5 | backedup |
| enwiki | !ClaudiaPascoal.png | 26203 | public | 66a8f68f1ac4708aaa600dcc5a5690836868a2c9 | backedup |
| enwiki | !Haunu.ogg | 13450 | public | 77577544869768e619829c4e9e6e0ddde94f9421 | backedup |
| enwiki | !Hero_(album).jpg | 38664 | public | c42574f1639e0bd96749f0ac3e76b69573ce2ba3 | backedup |
| enwiki | !Women_Art_Revolution_(documentary_film)_poster_art.jpg | 15446 | public | fd33557daf97103c72401e7a33723360cd0db9f4 | backedup |
| enwiki | !_(The_Song_Formely_Known_As)_by_Regurgitator.png | 146525 | public | c491daf7642bfa1d192b22bc83cc41c721d1092e | backedup |
| enwiki | ""Motor-Cycle"_LP_cover-Lotti_Golden.jpg | 17001 | public | dbc8dcac96e647d0dfcba702c88e86a0935e2d88 | backedup |
+-----------+---------------------------------------------------------+--------+-------------+------------------------------------------+--------------------+
10 rows in set (0.035 sec)