I have noticed the following slow API query on 10.4 (db1107):
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 */
That query on 10.4 takes around 28 seconds, whereas on 10.1 takes around 0.1.
Examining the query plan, it is identical, but 10.4 shows the following difference:
+------+-------------+---------------+--------------+-----------------------------------------------+----------------------+---------+------------------------------+----------+-------------------------------------------------+ | 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)
Looks like it is a new feature on 10.4: https://mariadb.com/kb/en/rowid-filtering-optimization/ but in this case it is causing the query to run slower.
By, changing it on the optimizer and running the query again, it runs as fast as 10.1:
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 | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) 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)
Logging out from that session, and running the query again with the original optimizer settings:
501 rows in set (25.38 sec)