|
We noticed a slow query:
SELECT /* ApiQueryExtLinksUsage::run */ el_index_60, el_id, page_id, page_namespace, page_title, el_to FROM `page`, `externallinks` WHERE (page_id=el_from) AND (el_index_60 LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) AND (el_index LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) ORDER BY el_index_60, el_id LIMIT 501 /* ebd959f27d0c6fd989d1561b40ae8a3b db1107 enwiki 24s */
|
This query involves the following tables:
*************************** 1. row ***************************
|
Table: pagelinks
|
Create Table: CREATE TABLE `pagelinks` (
|
`pl_from` int(8) unsigned NOT NULL DEFAULT 0,
|
`pl_namespace` int(11) NOT NULL DEFAULT 0,
|
`pl_title` varbinary(255) NOT NULL DEFAULT '',
|
`pl_from_namespace` int(11) NOT NULL DEFAULT 0,
|
PRIMARY KEY (`pl_from`,`pl_namespace`,`pl_title`),
|
KEY `pl_namespace` (`pl_namespace`,`pl_title`,`pl_from`),
|
KEY `pl_backlinks_namespace` (`pl_from_namespace`,`pl_namespace`,`pl_title`,`pl_from`)
|
) ENGINE=InnoDB DEFAULT CHARSET=binary
|
*************************** 1. row ***************************
|
Table: page
|
Create Table: CREATE TABLE `page` (
|
`page_id` int(8) unsigned NOT NULL AUTO_INCREMENT,
|
`page_namespace` int(11) NOT NULL DEFAULT 0,
|
`page_title` varbinary(255) NOT NULL DEFAULT '',
|
`page_restrictions` tinyblob NOT NULL,
|
`page_is_redirect` tinyint(1) unsigned NOT NULL DEFAULT 0,
|
`page_is_new` tinyint(1) unsigned NOT NULL DEFAULT 0,
|
`page_random` double unsigned NOT NULL DEFAULT 0,
|
`page_touched` varbinary(14) NOT NULL DEFAULT '',
|
`page_links_updated` varbinary(14) DEFAULT NULL,
|
`page_latest` int(8) unsigned NOT NULL DEFAULT 0,
|
`page_len` int(8) unsigned NOT NULL DEFAULT 0,
|
`page_content_model` varbinary(32) DEFAULT NULL,
|
`page_lang` varbinary(35) DEFAULT NULL,
|
PRIMARY KEY (`page_id`),
|
UNIQUE KEY `name_title` (`page_namespace`,`page_title`),
|
KEY `page_random` (`page_random`),
|
KEY `page_len` (`page_len`),
|
KEY `page_redirect_namespace_len` (`page_is_redirect`,`page_namespace`,`page_len`)
|
) ENGINE=InnoDB AUTO_INCREMENT=63170169 DEFAULT CHARSET=binary
|
The optimizer flags we had were:
root@db1107.eqiad.wmnet[enwiki]> select @@optimizer_switch;
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| @@optimizer_switch |
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on |
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
The following query has this execution plan:
+------+-------------+---------------+--------------+-----------------------------------------------+----------------------+---------+------------------------------+----------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------------+--------------+-----------------------------------------------+----------------------+---------+------------------------------+----------+-------------------------------------------------+
|
| 1 | SIMPLE | externallinks | range|filter | el_from,el_index,el_index_60,el_from_index_60 | el_index|el_index_60 | 62|62 | NULL | 553 (0%) | Using where; Using filesort; Using rowid filter |
|
| 1 | SIMPLE | page | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.externallinks.el_from | 1 | |
|
+------+-------------+---------------+--------------+-----------------------------------------------+----------------------+---------+------------------------------+----------+-------------------------------------------------+
|
2 rows in set (0.00 sec)
|
That query was taking around 25 seconds.
By turning off rowid_filter, the query doesn't change its query plan, but the execution time takes 0.01 to run:
root@db1107.eqiad.wmnet[enwiki]> set session optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=off,condition_pushdown_from_having=on';
|
Query OK, 0 rows affected (0.00 sec)
|
|
<query snip>
|
501 rows in set (0.00 sec)
|
|
|
root@db1107.eqiad.wmnet[enwiki]> explain SELECT /* ApiQueryExtLinksUsage::run */ el_index_60, el_id, page_id, page_namespace, page_title, el_to FROM `page`, `externallinks` WHERE (page_id=el_from) AND (el_index_60 LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) AND (el_index LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) ORDER BY el_index_60, el_id LIMIT 501 /* ebd959f27d0c6fd989d1561b40ae8a3b db1107 enwiki 24s */;
|
+------+-------------+---------------+--------+-----------------------------------------------+----------+---------+------------------------------+------+-----------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------------+--------+-----------------------------------------------+----------+---------+------------------------------+------+-----------------------------+
|
| 1 | SIMPLE | externallinks | range | el_from,el_index,el_index_60,el_from_index_60 | el_index | 62 | NULL | 553 | Using where; Using filesort |
|
| 1 | SIMPLE | page | eq_ref | PRIMARY | PRIMARY | 4 | enwiki.externallinks.el_from | 1 | |
|
+------+-------------+---------------+--------+-----------------------------------------------+----------+---------+------------------------------+------+-----------------------------+
|
2 rows in set (0.00 sec)
|
|
Looks like that rowid_filter=ON is making the query super slow.
This is a full optimizer trace with rowid_filter=ON:
root@db1107.eqiad.wmnet[enwiki]> select * from information_schema.optimizer_trace\G
|
*************************** 1. row ***************************
|
QUERY: SELECT el_index_60, el_id, page_id, page_namespace, page_title, el_to FROM `page`, `externallinks` WHERE (page_id=el_from) AND (el_index_60 LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) AND (el_index LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) ORDER BY el_index_60, el_id LIMIT 501
|
TRACE: {
|
"steps": [
|
{
|
"join_preparation": {
|
"select_id": 1,
|
"steps": [
|
{
|
"expanded_query": "select externallinks.el_index_60 AS el_index_60,externallinks.el_id AS el_id,`page`.page_id AS page_id,`page`.page_namespace AS page_namespace,`page`.page_title AS page_title,externallinks.el_to AS el_to from `page` join externallinks where `page`.page_id = externallinks.el_from and externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`' order by externallinks.el_index_60,externallinks.el_id limit 501"
|
}
|
]
|
}
|
},
|
{
|
"join_optimization": {
|
"select_id": 1,
|
"steps": [
|
{
|
"condition_processing": {
|
"condition": "WHERE",
|
"original_condition": "`page`.page_id = externallinks.el_from and externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`'",
|
"steps": [
|
{
|
"transformation": "equality_propagation",
|
"resulting_condition": "externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`' and multiple equal(`page`.page_id, externallinks.el_from)"
|
},
|
{
|
"transformation": "constant_propagation",
|
"resulting_condition": "externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`' and multiple equal(`page`.page_id, externallinks.el_from)"
|
},
|
{
|
"transformation": "trivial_condition_removal",
|
"resulting_condition": "externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`' and multiple equal(`page`.page_id, externallinks.el_from)"
|
}
|
]
|
}
|
},
|
{
|
"table_dependencies": [
|
{
|
"table": "page",
|
"row_may_be_null": false,
|
"map_bit": 0,
|
"depends_on_map_bits": []
|
},
|
{
|
"table": "externallinks",
|
"row_may_be_null": false,
|
"map_bit": 1,
|
"depends_on_map_bits": []
|
}
|
]
|
},
|
{
|
"ref_optimizer_key_uses": [
|
{
|
"table": "page",
|
"field": "page_id",
|
"equals": "externallinks.el_from",
|
"null_rejecting": false
|
},
|
{
|
"table": "page",
|
"field": "page_id",
|
"equals": "externallinks.el_from",
|
"null_rejecting": false
|
},
|
{
|
"table": "externallinks",
|
"field": "el_from",
|
"equals": "`page`.page_id",
|
"null_rejecting": false
|
},
|
{
|
"table": "externallinks",
|
"field": "el_from",
|
"equals": "`page`.page_id",
|
"null_rejecting": false
|
},
|
{
|
"table": "externallinks",
|
"field": "el_from",
|
"equals": "`page`.page_id",
|
"null_rejecting": false
|
}
|
]
|
},
|
{
|
"rows_estimation": [
|
{
|
"table": "page",
|
"table_scan": {
|
"rows": 42067094,
|
"cost": 405050
|
}
|
},
|
{
|
"table": "externallinks",
|
"range_analysis": {
|
"table_scan": {
|
"rows": 138944656,
|
"cost": 3.03e7
|
},
|
"potential_range_indexes": [
|
{
|
"index": "PRIMARY",
|
"usable": false,
|
"cause": "not applicable"
|
},
|
{
|
"index": "el_from",
|
"usable": false,
|
"cause": "not applicable"
|
},
|
{
|
"index": "el_to",
|
"usable": false,
|
"cause": "not applicable"
|
},
|
{
|
"index": "el_index",
|
"usable": true,
|
"key_parts": ["el_index"]
|
},
|
{
|
"index": "el_index_60",
|
"usable": true,
|
"key_parts": ["el_index_60", "el_id"]
|
},
|
{
|
"index": "el_from_index_60",
|
"usable": false,
|
"cause": "not applicable"
|
}
|
],
|
"setup_range_conditions": [],
|
"group_index_range": {
|
"chosen": false,
|
"cause": "not single_table"
|
},
|
"analyzing_range_alternatives": {
|
"range_scan_alternatives": [
|
{
|
"index": "el_index",
|
"ranges": [
|
"(http://edu.utexas.cs.www./) <= (el_index) <= (http://edu.utexas.cs.www./\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF)"
|
],
|
"rowid_ordered": false,
|
"using_mrr": false,
|
"index_only": false,
|
"rows": 553,
|
"cost": 696.95,
|
"chosen": true
|
},
|
{
|
"index": "el_index_60",
|
"ranges": [
|
"(http://edu.utexas.cs.www./) <= (el_index_60) <= (http://edu.utexas.cs.www./\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF)"
|
],
|
"rowid_ordered": false,
|
"using_mrr": false,
|
"index_only": false,
|
"rows": 553,
|
"cost": 697.97,
|
"chosen": false,
|
"cause": "cost"
|
}
|
],
|
"analyzing_roworder_intersect": {
|
"cause": "too few roworder scans"
|
},
|
"analyzing_index_merge_union": []
|
},
|
"chosen_range_access_summary": {
|
"range_access_plan": {
|
"type": "range_scan",
|
"index": "el_index",
|
"rows": 553,
|
"ranges": [
|
"(http://edu.utexas.cs.www./) <= (el_index) <= (http://edu.utexas.cs.www./\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF)"
|
]
|
},
|
"rows_for_plan": 553,
|
"cost_for_plan": 696.95,
|
"chosen": true
|
}
|
}
|
},
|
{
|
"selectivity_for_indexes": [
|
{
|
"index_name": "el_index",
|
"selectivity_from_index": 4e-6
|
},
|
{
|
"index_name": "el_index_60",
|
"selectivity_from_index": 4e-6
|
}
|
],
|
"selectivity_for_columns": [],
|
"cond_selectivity": 2e-11
|
}
|
]
|
},
|
{
|
"considered_execution_plans": [
|
{
|
"plan_prefix": [],
|
"table": "externallinks",
|
"best_access_path": {
|
"considered_access_paths": [
|
{
|
"access_type": "range",
|
"resulting_rows": 0.0022,
|
"cost": 250.93,
|
"chosen": true,
|
"use_tmp_table": true
|
}
|
],
|
"chosen_access_method": {
|
"type": "range",
|
"records": 0.0022,
|
"cost": 250.93,
|
"uses_join_buffering": false,
|
"filter_used": true
|
}
|
},
|
"rows_for_plan": 0.0022,
|
"cost_for_plan": 250.93,
|
"rest_of_plan": [
|
{
|
"plan_prefix": ["externallinks"],
|
"table": "page",
|
"best_access_path": {
|
"considered_access_paths": [
|
{
|
"access_type": "eq_ref",
|
"index": "PRIMARY",
|
"rows": 1,
|
"cost": 0.0022,
|
"chosen": true
|
},
|
{
|
"type": "scan",
|
"chosen": false,
|
"cause": "cost"
|
}
|
],
|
"chosen_access_method": {
|
"type": "eq_ref",
|
"records": 1,
|
"cost": 0.0022,
|
"uses_join_buffering": false,
|
"filter_used": false
|
}
|
},
|
"rows_for_plan": 0.0022,
|
"cost_for_plan": 250.94,
|
"estimated_join_cardinality": 0.0022
|
}
|
]
|
},
|
{
|
"plan_prefix": [],
|
"table": "page",
|
"best_access_path": {
|
"considered_access_paths": [
|
{
|
"access_type": "scan",
|
"resulting_rows": 4.21e7,
|
"cost": 405050,
|
"chosen": true
|
}
|
],
|
"chosen_access_method": {
|
"type": "scan",
|
"records": 4.21e7,
|
"cost": 405050,
|
"uses_join_buffering": false,
|
"filter_used": false
|
}
|
},
|
"rows_for_plan": 4.21e7,
|
"cost_for_plan": 8.82e6,
|
"pruned_by_cost": true
|
}
|
]
|
},
|
{
|
"best_join_order": ["externallinks", "page"]
|
},
|
{
|
"table": "externallinks",
|
"range_analysis": {
|
"table_scan": {
|
"rows": 138944656,
|
"cost": 2e308
|
},
|
"potential_range_indexes": [
|
{
|
"index": "PRIMARY",
|
"usable": false,
|
"cause": "not applicable"
|
},
|
{
|
"index": "el_from",
|
"usable": false,
|
"cause": "not applicable"
|
},
|
{
|
"index": "el_to",
|
"usable": false,
|
"cause": "not applicable"
|
},
|
{
|
"index": "el_index",
|
"usable": false,
|
"cause": "not applicable"
|
},
|
{
|
"index": "el_index_60",
|
"usable": true,
|
"key_parts": ["el_index_60", "el_id"]
|
},
|
{
|
"index": "el_from_index_60",
|
"usable": false,
|
"cause": "not applicable"
|
}
|
],
|
"setup_range_conditions": [],
|
"analyzing_range_alternatives": {
|
"range_scan_alternatives": [
|
{
|
"index": "el_index_60",
|
"ranges": [
|
"(http://edu.utexas.cs.www./) <= (el_index_60) <= (http://edu.utexas.cs.www./\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF)"
|
],
|
"rowid_ordered": false,
|
"using_mrr": false,
|
"index_only": false,
|
"rows": 553,
|
"cost": 697.97,
|
"chosen": true
|
}
|
]
|
},
|
"chosen_range_access_summary": {
|
"range_access_plan": {
|
"type": "range_scan",
|
"index": "el_index_60",
|
"rows": 553,
|
"ranges": [
|
"(http://edu.utexas.cs.www./) <= (el_index_60) <= (http://edu.utexas.cs.www./\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF\xFF)"
|
]
|
},
|
"rows_for_plan": 553,
|
"cost_for_plan": 697.97,
|
"chosen": true
|
}
|
}
|
},
|
{
|
"attaching_conditions_to_tables": {
|
"original_condition": "`page`.page_id = externallinks.el_from and externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`'",
|
"attached_conditions_computation": [],
|
"attached_conditions_summary": [
|
{
|
"table": "externallinks",
|
"attached": "externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`'"
|
},
|
{
|
"table": "page",
|
"attached": null
|
}
|
]
|
}
|
},
|
{
|
"reconsidering_access_paths_for_index_ordering": {
|
"clause": "ORDER BY",
|
"fanout": 1,
|
"read_time": 250.93,
|
"table": "externallinks",
|
"rows_estimation": 553,
|
"possible_keys": [
|
{
|
"index": "PRIMARY",
|
"can_resolve_order": false,
|
"cause": "not usable index for the query"
|
},
|
{
|
"index": "el_from",
|
"can_resolve_order": false,
|
"cause": "not usable index for the query"
|
},
|
{
|
"index": "el_to",
|
"can_resolve_order": false,
|
"cause": "not usable index for the query"
|
},
|
{
|
"index": "el_index",
|
"can_resolve_order": false,
|
"cause": "not usable index for the query"
|
},
|
{
|
"index": "el_index_60",
|
"can_resolve_order": true,
|
"updated_limit": 138944656,
|
"range_scan_time": 697.97,
|
"index_scan_time": 697.97,
|
"usable": false,
|
"cause": "cost"
|
},
|
{
|
"index": "el_from_index_60",
|
"can_resolve_order": false,
|
"cause": "order can not be resolved by key"
|
}
|
]
|
}
|
}
|
]
|
}
|
},
|
{
|
"join_execution": {
|
"select_id": 1,
|
"steps": []
|
}
|
}
|
]
|
}
|
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
|
INSUFFICIENT_PRIVILEGES: 0
|
|