Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.4.12
-
debian buster
Description
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
|
Attachments
Issue Links
- causes
-
-
- Closed
-
-
-
- Closed
-
- relates to
-
MDEV-16402 Support Index Condition Pushdown for clustered PK scans
-
- Confirmed
-
-
-
- Closed
-
Activity
Some notes:
Re-formatting the query to be readable:
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
|
The query plan:
It's using range access over
(el_index LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) |
and a rowid filter over
(el_index_60 LIKE 'http://edu.utexas.cs.www./%' ESCAPE '`' ) AND |
the conditions are obviously correlated, so using the rowid filter won't bring any benefits, but it should not be a big slowdown either. (Scan roughly the same range in two indexes instead of one means 2x slowdown at most, actually much less)
One visible deficiency is that el_index_60 could be used to resolve the ORDER BY ... LIMIT but is not used for that purpose...
"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"
|
}
|
"reconsidering_access_paths_for_index_ordering": {
|
{
|
"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"
|
}
|
but the estimate of #records on both indexes is about ~ 500 rows, so this would not explain the slowdown.
marostegui, also could you please provide definition of externallinks table? I see https://www.mediawiki.org/wiki/Manual:Externallinks_table but which variant are you experiencing this on?
Sorry, I thought I shared externallinks.
Table: externallinks
|
Create Table: CREATE TABLE `externallinks` (
|
`el_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
`el_from` int(8) unsigned NOT NULL DEFAULT 0,
|
`el_to` blob NOT NULL,
|
`el_index` blob NOT NULL,
|
`el_index_60` varbinary(60) NOT NULL,
|
PRIMARY KEY (`el_id`),
|
KEY `el_from` (`el_from`,`el_to`(40)),
|
KEY `el_to` (`el_to`(60),`el_from`),
|
KEY `el_index` (`el_index`(60)),
|
KEY `el_index_60` (`el_index_60`,`el_id`),
|
KEY `el_from_index_60` (`el_from`,`el_index_60`,`el_id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=534607547 DEFAULT CHARSET=binary
|
And here you are the ANALYZE JSON.
Thanks a lot for looking into this so fast!
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=on,condition_pushdown_from_having=on';
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
root@db1107.eqiad.wmnet[enwiki]> ANALYZE FORMAT=JSON 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 */
|
-> ;
|
|
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| ANALYZE |
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| {
|
"query_block": {
|
"select_id": 1,
|
"r_loops": 1,
|
"r_total_time_ms": 102185,
|
"read_sorted_file": {
|
"r_rows": 501,
|
"filesort": {
|
"sort_key": "externallinks.el_index_60, externallinks.el_id",
|
"r_loops": 1,
|
"r_total_time_ms": 102179,
|
"r_used_priority_queue": false,
|
"r_output_rows": 553,
|
"r_buffer_size": "2047Kb",
|
"table": {
|
"table_name": "externallinks",
|
"access_type": "range",
|
"possible_keys": [
|
"el_from",
|
"el_index",
|
"el_index_60",
|
"el_from_index_60"
|
],
|
"key": "el_index",
|
"key_length": "62",
|
"used_key_parts": ["el_index"],
|
"rowid_filter": {
|
"range": {
|
"key": "el_index_60",
|
"used_key_parts": ["el_index_60"]
|
},
|
"rows": 553,
|
"selectivity_pct": 4e-4,
|
"r_rows": 553,
|
"r_selectivity_pct": 5.1e-4,
|
"r_buffer_size": 276,
|
"r_filling_time_ms": 0.1376
|
},
|
"r_loops": 1,
|
"rows": 553,
|
"r_rows": 553,
|
"r_total_time_ms": 102182,
|
"filtered": 4e-4,
|
"r_filtered": 100,
|
"attached_condition": "externallinks.el_index_60 like 'http://edu.utexas.cs.www./%' escape '`' and externallinks.el_index like 'http://edu.utexas.cs.www./%' escape '`'"
|
}
|
}
|
},
|
"table": {
|
"table_name": "page",
|
"access_type": "eq_ref",
|
"possible_keys": ["PRIMARY"],
|
"key": "PRIMARY",
|
"key_length": "4",
|
"used_key_parts": ["page_id"],
|
"ref": ["enwiki.externallinks.el_from"],
|
"r_loops": 501,
|
"rows": 1,
|
"r_rows": 1,
|
"r_total_time_ms": 0.7418,
|
"filtered": 100,
|
"r_filtered": 100
|
}
|
}
|
} |
|
Ok, so it shows that all of the time was spent reading the externallinks table.
The rowid filter:
"rowid_filter": {
|
"range": {
|
"key": "el_index_60",
|
"used_key_parts": ["el_index_60"]
|
},
|
"rows": 553,
|
"selectivity_pct": 4e-4,
|
"r_rows": 553,
|
"r_selectivity_pct": 5.1e-4,
|
"r_buffer_size": 276,
|
"r_filling_time_ms": 0.1376
|
},
|
It's small and doesn't take much time to populate.
But why is it so selective? It shows
"r_selectivity_pct": 5.1e-4,
|
which doesn't seem realistic. We are using range access and then the rowid filter built from the same condition. Its selectivity should be close to 1...
Reproducible on the dataset from here: https://dumps.wikimedia.org/enwiki/latest/ . The datadir is ~120 GB.
Trying
create temporary table tmp1 as select * from information_schema.session_status;
|
<query>
|
create temporary table tmp2 as select * from information_schema.session_status;
|
|
|
select
|
tmp1.variable_name,
|
tmp2.variable_value - tmp1.variable_value as diff
|
from tmp1, tmp2
|
where tmp2.VARIABLE_NAME=tmp1.VARIABLE_NAME and tmp2.variable_value != tmp1.variable_value;
|
The interesting parts are:
+------------------------------------------------+------------+
|
| variable_name | diff |
|
+------------------------------------------------+------------+
|
| HANDLER_READ_KEY | 448 |
|
| HANDLER_READ_NEXT | 1106 |
|
| HANDLER_READ_RND | 501 |
|
| HANDLER_READ_RND_NEXT | 543 |
|
|
|
| HANDLER_TMP_WRITE | 542 |
|
| HANDLER_WRITE | 542 |
|
|
|
| INNODB_BUFFER_POOL_READ_REQUESTS | 866783 |
|
| INNODB_BUFFER_POOL_READS | 236094 |
|
|
|
| INNODB_DATA_READ | 3903356928 |
|
| INNODB_DATA_READS | 238242 |
|
|
|
| INNODB_PAGES_READ | 238242 |
|
The amount of data that was read from InnoDB is too much.
I ran the query under debugger and handler_rowid_filter_check was called 70K times (after which I interrupted the query).
Ok, the bug is in row_search_idx_cond_check. There is no pushed index condition (prebuilt->idx_cond==NULL), it checks the rowid filer, the row is not a match, so it continues the scan.
Unlike ICP, it doesn't check for ICP_OUT_OF_RANGE condition.
Another question is, why index condition pushdown is not used?
(gdb) wher
|
#0 uses_index_fields_only (item=0x62b0000a3a68, tbl=0x6200000330f0, keyno=3, other_tbls_ok=true) at /home/psergey/dev-git/10.4-cl2/sql/opt_index_cond_pushdown.cc:115
|
#1 0x0000555556d051ba in uses_index_fields_only (item=0x62b0000a3d10, tbl=0x6200000330f0, keyno=3, other_tbls_ok=true) at /home/psergey/dev-git/10.4-cl2/sql/opt_index_cond_pushdown.cc:83
|
#2 0x0000555556d06435 in make_cond_for_index (thd=0x62b00009a270, cond=0x62b0000a3d10, table=0x6200000330f0, keyno=3, other_tbls_ok=true) at /home/psergey/dev-git/10.4-cl2/sql/opt_index_cond_pushdown.cc:257
|
#3 0x0000555556d05d41 in make_cond_for_index (thd=0x62b00009a270, cond=0x62900010b4a0, table=0x6200000330f0, keyno=3, other_tbls_ok=true) at /home/psergey/dev-git/10.4-cl2/sql/opt_index_cond_pushdown.cc:203
|
#4 0x0000555556d07614 in push_index_cond (tab=0x629000109fc0, keyno=3) at /home/psergey/dev-git/10.4-cl2/sql/opt_index_cond_pushdown.cc:367
|
#5 0x00005555569734e5 in make_join_readinfo (join=0x62b0000a5248, options=4, no_jbuf_after=2) at /home/psergey/dev-git/10.4-cl2/sql/sql_select.cc:13152
|
#6 0x0000555556921d6c in JOIN::optimize_stage2 (this=0x62b0000a5248) at /home/psergey/dev-git/10.4-cl2/sql/sql_select.cc:2782
|
#7 0x000055555691c47e in JOIN::optimize_inner (this=0x62b0000a5248) at /home/psergey/dev-git/10.4-cl2/sql/sql_select.cc:2277
|
#8 0x000055555691509b in JOIN::optimize (this=0x62b0000a5248) at /home/psergey/dev-git/10.4-cl2/sql/sql_select.cc:1598
|
#9 0x0000555556935ad5 in mysql_select (thd=0x62b00009a270, tables=0x62b0000a21f0, wild_num=0, fields=..., conds=0x62b0000a3900, og_num=2, order=0x62b0000a47f0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x62b0000a4b20, unit=0x62b00009e198, select_lex=0x62b0000a1580) at /home/psergey/dev-git/10.4-cl2/sql/sql_select.cc:4652
|
#10 0x00005555569da57e in mysql_explain_union (thd=0x62b00009a270, unit=0x62b00009e198, result=0x62b0000a4b20) at /home/psergey/dev-git/10.4-cl2/sql/sql_select.cc:26922
|
#11 0x000055555687974a in execute_sqlcom_select (thd=0x62b00009a270, all_tables=0x62b0000a21f0) at /home/psergey/dev-git/10.4-cl2/sql/sql_parse.cc:6298
|
#12 0x0000555556865d73 in mysql_execute_command (thd=0x62b00009a270) at /home/psergey/dev-git/10.4-cl2/sql/sql_parse.cc:3898
|
#13 0x0000555556883772 in mysql_parse (thd=0x62b00009a270, rawbuf=0x62b0000a1290 "explain 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./%' ES"..., length=318, parser_state=0x7ffab1caa4c0, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.4-cl2/sql/sql_parse.cc:7900
|
#14 0x00005555568581c7 in dispatch_command (command=COM_QUERY, thd=0x62b00009a270, packet=0x6290000f0271 "", packet_length=318, is_com_multi=false, is_next_command=false) at /home/psergey/dev-git/10.4-cl2/sql/sql_parse.cc:1841
|
#15 0x00005555568548e1 in do_command (thd=0x62b00009a270) at /home/psergey/dev-git/10.4-cl2/sql/sql_parse.cc:1359
|
#16 0x0000555556c362c5 in do_handle_one_connection (connect=0x61100004a570) at /home/psergey/dev-git/10.4-cl2/sql/sql_connect.cc:1412
|
#17 0x0000555556c35b63 in handle_one_connection (arg=0x61100004a570) at /home/psergey/dev-git/10.4-cl2/sql/sql_connect.cc:1316
|
#18 0x00007ffff5ae36db in start_thread (arg=0x7ffab1cad300) at pthread_create.c:463
|
#19 0x00007ffff457188f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
|
(gdb) p dbug_print_item(item)
|
InnoDB: ###### Diagnostic info printed to the standard error stream
|
$39 = 0x555559fd5160 <dbug_item_print_buf> "externallinks.el_index"
|
(gdb) p field->field_name
|
$40 = {str = 0x6190000b74ed "el_index", length = 8}
|
(gdb) p field->part_of_key
|
$41 = {map = 0}
|
(gdb) list
|
110 return TRUE;
|
111 /*
|
112 The below is probably a repetition - the first part checks the
|
113 other two, but let's play it safe:
|
114 */
|
115 if(!field->part_of_key.is_set(keyno) ||
|
116 field->type() == MYSQL_TYPE_GEOMETRY ||
|
117 field->type() == MYSQL_TYPE_BLOB)
|
118 return FALSE;
|
119 KEY *key_info= tbl->key_info + keyno;
|
(gdb) p field->type()
|
$47 = MYSQL_TYPE_BLOB
|
Ok it's a blob field, and the index is on the prefix of it.
psergey, why is index condition pushdown disabled on a prefix of a BLOB column? As far as I can tell, it should be possible to adjust the filters so that a false negative will never be returned.
Is there currently a bug related to TEXT columns? If you are searching for LIKE 'foobar%' and the column prefix in the first column of the index is only 5 characters long, then we can push down a search for LIKE 'fooba%' and must validate that the returned results actually satisfy the condition LIKE 'foobar%'.
Index condition pushdown should technically work for clustered indexes. As noted in MDEV-16402, pushdown has been disabled on the clustered index because the performance can suffer if the filters are not cleaned up in advance.
Sergei Petrunia, why is index condition pushdown disabled on a prefix of a BLOB column?
Because the index only has a prefix of the blob column. In this example, it is:
KEY `el_index` (`el_index`(60)),
|
and in general it is quite hard to check if cond(X) is the same as cond(LEFT(X(60)). We could come up with a check for sargable predicates ( LIKE 'foo%')... but this won't be of much use: for those predicates the optimizer has constructed ranges.
Patch: http://lists.askmonty.org/pipermail/commits/2020-February/014172.html . igor, please review.
Second variant of the patch with all input addressed: http://lists.askmonty.org/pipermail/commits/2020-March/014210.html . igor, please review.
Do you guys think this will make it to the 10.4.13 release scheduled by the end of April?
Thank you!
Note: the MyISAM/Aria part of the patch doesnt' work correctly, it needs to be adjusted. I''ll do that
Upgraded our Production system from 10.4.7 to the latest 10.4.13 last night and afterwards noticed a very slow performing query.
The query now took 6 seconds where as it normally took +- 80ms before the upgrade.
I traced it to the rowid_filter optimization and after switching it OFF the query execution was back to +- 80ms again.
We use InnoDB.
Not sure why it would suddenly start to use rowid_filter.
So it does not seem that this fix made it into 10.4.13 or there are still issues with it?
In our environment, 10.4.13 did fix the issue.
The original reported query being slow with this flag ON is no longer slow after upgrading to 10.4.13
Per: https://jira.mariadb.org/projects/MDEV/versions/24223 it does include the fix for this.
I experienced the slowness related to this flag on a magento 2 database before upgrading to 10.4.13 version (confirmed by disabling in my.cnf and slowness disappeared). I upgraded to 10.4.13 and removed the disabling of this flag as this issue said it was resolved, however I experienced the same slowness. Disabling the flag again resolved the issue. maria DB 10.4.13 / Arch Linux. query time went from over 30s to < 1s with the flag disabled. I do not believe this issue is fully resolved.
Edit: recorded actual time for queries (this was a count query so returns 1 line):
with flag on (default): 1 row in set (3 min 24.590 sec)
with flag off: 1 row in set (0.519 sec)
Only change between the above 2 tests was adding the following to `/etc/my.cnf`
[mysqld]
|
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=off,mrr_cost_based=off,mrr_sort_keys=off,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"
|
paulmourer, would it be possible for you to share the query that causes it and its EXPLAIN (for the slow execution, when ran with rowid_filter=on) ?
psergey, This is with the rowid_filter on. in both of these queries i have scrubbed the database name and substituted DB_NAME
explain SELECT COUNT(DISTINCT e.entity_id) FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND (`at_status_default`.`attribute_id` = '96') AND `at_status_default`.`store_id` = 0 LEFT JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '96') AND (`at_status`.`store_id` = 1) WHERE (IF(at_status.value_id > 0, at_status.value, at_status_default.value) = 1) AND (`e`.`attribute_set_id` = '4');
|
+------+-------------+-------------------+------------+----------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------+---------+-------------------------------+------------+---------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------------------+------------+----------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------+---------+-------------------------------+------------+---------------------------------+
|
| 1 | SIMPLE | e | ref | PRIMARY,CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID | CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID | 2 | const | 20808 | Using index |
|
| 1 | SIMPLE | at_status | ref|filter | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID | CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID|CATALOG_PRODUCT_ENTITY_INT_STORE_ID | 2|2 | const | 78010 (0%) | Using where; Using rowid filter |
|
| 1 | SIMPLE | at_status_default | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | DB_NAME.e.entity_id,const,const | 1 | Using where |
|
+------+-------------+-------------------+------------+----------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------+---------+-------------------------------+------------+---------------------------------+
|
3 rows in set (0.002 sec)
|
this is without the rowid filter:
explain SELECT COUNT(DISTINCT e.entity_id) FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND (`at_status_default`.`attribute_id` = '96') AND `at_status_default`.`store_id` = 0 LEFT JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '96') AND (`at_status`.`store_id` = 1) WHERE (IF(at_status.value_id > 0, at_status.value, at_status_default.value) = 1) AND (`e`.`attribute_set_id` = '4');
|
+------+-------------+-------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+-------------------------------+-------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+-------------------------------+-------+-------------+
|
| 1 | SIMPLE | e | ref | PRIMARY,CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID | CATALOG_PRODUCT_ENTITY_ATTRIBUTE_SET_ID | 2 | const | 20808 | Using index |
|
| 1 | SIMPLE | at_status | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | DB_NAME.e.entity_id,const,const | 1 | |
|
| 1 | SIMPLE | at_status_default | eq_ref | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID | CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID | 8 | DB_NAME.e.entity_id,const,const | 1 | Using where |
|
+------+-------------+-------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------+---------+-------------------------------+-------+-------------+
|
3 rows in set (0.001 sec)
|
|
Hi Paul,
Your problem is definitely not the same as of bug MDEV-21794. Could you please submit a new bug report in which you'll repeat what you said here and you'll confirm that you have such indexes
CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID (2 columns)
CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID (1 column)
CATALOG_PRODUCT_ENTITY_INT_STORE_ID (1 column)
and you'll specify over which columns they are.
Could you also provide us with statistics on these indexes (run SHOW INDEXES FROM catalog_product_entity_int) ?
marostegui, would it be possible for you to run ANALYZE FORMAT=JSON <select query> (with rowid_filter=ON) and share the output?