The MediaWiki-Action-API tag is correct. T16102: API query for template usage involving multiple titles and namespace filter times out seems to be the bug that the addition was trying to fix. The situation there isn't terribly clear, T16102#182288 says it was using the correct index but T16102#182314 guesses that it wasn't, and nowhere was the actual EXPLAIN output copied into the bug.

I agree, let's try getting rid of the index forcing here. I'll trust you to keep an eye on the database and let me know if this turns out to cause bad plans for some versions of the query.

Looking through the rest of the core API:

Change 347850 had a related patch set uploaded (by Anomie):
[mediawiki/core@master] ApiQueryLinks: Remove index forcing

https://gerrit.wikimedia.org/r/347850

Hey Anomie - thanks for the fast response, as always!

So, the query forcing the index on db1088 (original table schema with the UNIQUE):

+------+-------------+---------------+-------+---------------+---------+---------+------+------+------------------------------------------+
| id   | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows | Extra                                    |
+------+-------------+---------------+-------+---------------+---------+---------+------+------+------------------------------------------+
|    1 | SIMPLE      | templatelinks | range | tl_from       | tl_from | 265     | NULL |  124 | Using where; Using index; Using filesort |
+------+-------------+---------------+-------+---------------+---------+---------+------+------+------------------------------------------+
1 row in set (0.01 sec)

The query without forcing the index:

+------+-------------+---------------+-------+----------------------+--------------+---------+------+------+------------------------------------------+
| id   | select_type | table         | type  | possible_keys        | key          | key_len | ref  | rows | Extra                                    |
+------+-------------+---------------+-------+----------------------+--------------+---------+------+------+------------------------------------------+
|    1 | SIMPLE      | templatelinks | range | tl_from,tl_namespace | tl_namespace | 265     | NULL |  124 | Using where; Using index; Using filesort |
+------+-------------+---------------+-------+----------------------+--------------+---------+------+------+------------------------------------------+

And for the pagelinks table:

Forcing the index:

+------+-------------+-----------+------+---------------+---------+---------+-------------+------+--------------------------+
| id   | select_type | table     | type | possible_keys | key     | key_len | ref         | rows | Extra                    |
+------+-------------+-----------+------+---------------+---------+---------+-------------+------+--------------------------+
|    1 | SIMPLE      | pagelinks | ref  | pl_from       | pl_from | 8       | const,const |    1 | Using where; Using index |
+------+-------------+-----------+------+---------------+---------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

Without forcing it:

+------+-------------+-----------+------+----------------------+---------+---------+-------------+------+--------------------------+
| id   | select_type | table     | type | possible_keys        | key     | key_len | ref         | rows | Extra                    |
+------+-------------+-----------+------+----------------------+---------+---------+-------------+------+--------------------------+
|    1 | SIMPLE      | pagelinks | ref  | pl_from,pl_namespace | pl_from | 8       | const,const |    1 | Using where; Using index |
+------+-------------+-----------+------+----------------------+---------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

I would also suggest we do not merge/deploy your code this week, as there is holidays coming in tomorrow in Spain, so Jaime and myself will be away until next week. The issue is only happening with this server and it is fixed, so the risk is under control at the moment. Better probably to deploy after the DC switch, so we can have all the eyes on this host.

Thanks again for the fast response, really appreciated!

In T162774#3175251, @Marostegui wrote:

+------+-------------+---------------+-------+---------------+---------+---------+------+------+------------------------------------------+
| id   | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows | Extra                                    |
+------+-------------+---------------+-------+---------------+---------+---------+------+------+------------------------------------------+
|    1 | SIMPLE      | templatelinks | range | tl_from       | tl_from | 265     | NULL |  124 | Using where; Using index; Using filesort |
+------+-------------+---------------+-------+---------------+---------+---------+------+------+------------------------------------------+
1 row in set (0.01 sec)

I guess that was with the query in the description? Looking at the code, I could improve it to avoid the filesorting in that case by better taking into account when tl_namespace and/or tl_title are singular.

mysql:wikiadmin@db1088 [frwiki]> explain SELECT tl_from AS `pl_from`,tl_namespace AS `pl_namespace`,tl_title AS `pl_title`  FROM `templatelinks` FORCE INDEX (tl_from) WHERE tl_from IN (10635368, 10635369)  AND ((tl_namespace = 'xx' AND tl_title IN ('xx') ))  ORDER BY tl_from LIMIT 501;
+------+-------------+---------------+-------+---------------+---------+---------+------+------+--------------------------+
| id   | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+------+-------------+---------------+-------+---------------+---------+---------+------+------+--------------------------+
|    1 | SIMPLE      | templatelinks | range | tl_from       | tl_from | 265     | NULL |    2 | Using where; Using index |
+------+-------------+---------------+-------+---------------+---------+---------+------+------+--------------------------+

mysql:wikiadmin@db1088 [frwiki]> explain SELECT tl_from AS `pl_from`,tl_namespace AS `pl_namespace`,tl_title AS `pl_title`  FROM `templatelinks` FORCE INDEX (tl_from) WHERE tl_from IN ('10635368', '10635369')  AND ((tl_namespace = '10' AND tl_title IN ('B','E') ))  ORDER BY tl_from, tl_title LIMIT 501;
+------+-------------+---------------+-------+---------------+---------+---------+------+------+--------------------------+
| id   | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+------+-------------+---------------+-------+---------------+---------+---------+------+------+--------------------------+
|    1 | SIMPLE      | templatelinks | range | tl_from       | tl_from | 265     | NULL |    4 | Using where; Using index |
+------+-------------+---------------+-------+---------------+---------+---------+------+------+--------------------------+

mysql:wikiadmin@db1088 [frwiki]> explain SELECT tl_from AS `pl_from`,tl_namespace AS `pl_namespace`,tl_title AS `pl_title`  FROM `templatelinks` FORCE INDEX (tl_from) WHERE tl_from IN ('10635368', '10635369')  AND ((tl_namespace = '10' AND tl_title IN ('B','E') ) OR (tl_namespace = '11' AND tl_title IN ('B','E')))  ORDER BY tl_from, tl_namespace, tl_title LIMIT 501;
+------+-------------+---------------+-------+---------------+---------+---------+------+------+--------------------------+
| id   | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+------+-------------+---------------+-------+---------------+---------+---------+------+------+--------------------------+
|    1 | SIMPLE      | templatelinks | range | tl_from       | tl_from | 265     | NULL |    8 | Using where; Using index |
+------+-------------+---------------+-------+---------------+---------+---------+------+------+--------------------------+

The query without forcing the index:

+------+-------------+---------------+-------+----------------------+--------------+---------+------+------+------------------------------------------+
| id   | select_type | table         | type  | possible_keys        | key          | key_len | ref  | rows | Extra                                    |
+------+-------------+---------------+-------+----------------------+--------------+---------+------+------+------------------------------------------+
|    1 | SIMPLE      | templatelinks | range | tl_from,tl_namespace | tl_namespace | 265     | NULL |  124 | Using where; Using index; Using filesort |
+------+-------------+---------------+-------+----------------------+--------------+---------+------+------+------------------------------------------+

Unfortunately, it does still insist on making the filesorting index choice without the forcing.

mysql:wikiadmin@db1088 [frwiki]> explain SELECT tl_from AS `pl_from`,tl_namespace AS `pl_namespace`,tl_title AS `pl_title`  FROM `templatelinks` WHERE tl_from IN (/* 5000 page_ids */)  AND ((tl_namespace = '10' AND tl_title IN ('B','E') ))  ORDER BY tl_from, tl_title LIMIT 501;
+------+-------------+---------------+-------+----------------------+--------------+---------+------+-------+------------------------------------------+
| id   | select_type | table         | type  | possible_keys        | key          | key_len | ref  | rows  | Extra                                    |
+------+-------------+---------------+-------+----------------------+--------------+---------+------+-------+------------------------------------------+
|    1 | SIMPLE      | templatelinks | range | tl_from,tl_namespace | tl_namespace | 265     | NULL | 10000 | Using where; Using index; Using filesort |
+------+-------------+---------------+-------+----------------------+--------------+---------+------+-------+------------------------------------------+

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 10000 |
| Handler_read_last          | 0     |
| Handler_read_next          | 5544  |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+

In the worst case (5000 values for tl_from and 500 titles in the tl_title, with all the combinations actually present in templatelinks), that Handler_read_key might be 5000000, while with tl_from it stops much earlier. Although perhaps the improved optimizer in MariaDB 10.1 will realize this situation and choose the better index.

That will be solved when we implement a version that supports better query plans for IN (5.6, not sure which mariadb version).

For now, this is exactly the reason why I prefer IGNORE over FORCE (despite hating both). It is slightly more maintainable:

root@db1088.eqiad.wmnet[frwiki]> explain SELECT tl_from AS `pl_from`,tl_namespace AS `pl_namespace`,tl_title AS `pl_title`  FROM `templatelinks` IGNORE INDEX(tl_namespace) WHERE tl_from IN (1,2,3,4,5,6,7,8,9,10,11,12)  AND ((tl_namespace = '10' AND tl_title IN ('B','E') ))  ORDER BY tl_from, tl_title LIMIT 501\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: templatelinks
         type: range
possible_keys: tl_from
          key: tl_from
      key_len: 265
          ref: NULL
         rows: 24
        Extra: Using where; Using index
1 row in set (0.00 sec)

In T162774#3175378, @Anomie wrote:

In T162774#3175251, @Marostegui wrote:

+------+-------------+---------------+-------+---------------+---------+---------+------+------+------------------------------------------+
| id   | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows | Extra                                    |
+------+-------------+---------------+-------+---------------+---------+---------+------+------+------------------------------------------+
|    1 | SIMPLE      | templatelinks | range | tl_from       | tl_from | 265     | NULL |  124 | Using where; Using index; Using filesort |
+------+-------------+---------------+-------+---------------+---------+---------+------+------+------------------------------------------+
1 row in set (0.01 sec)

I guess that was with the query in the description? Looking at the code, I could improve it to avoid the filesorting in that case by better taking into account when tl_namespace and/or tl_title are singular.

Yes

mysql:wikiadmin@db1088 [frwiki]> explain SELECT tl_from AS `pl_from`,tl_namespace AS `pl_namespace`,tl_title AS `pl_title`  FROM `templatelinks` FORCE INDEX (tl_from) WHERE tl_from IN (10635368, 10635369)  AND ((tl_namespace = 'xx' AND tl_title IN ('xx') ))  ORDER BY tl_from LIMIT 501;
+------+-------------+---------------+-------+---------------+---------+---------+------+------+--------------------------+
| id   | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+------+-------------+---------------+-------+---------------+---------+---------+------+------+--------------------------+
|    1 | SIMPLE      | templatelinks | range | tl_from       | tl_from | 265     | NULL |    2 | Using where; Using index |
+------+-------------+---------------+-------+---------------+---------+---------+------+------+--------------------------+

mysql:wikiadmin@db1088 [frwiki]> explain SELECT tl_from AS `pl_from`,tl_namespace AS `pl_namespace`,tl_title AS `pl_title`  FROM `templatelinks` FORCE INDEX (tl_from) WHERE tl_from IN ('10635368', '10635369')  AND ((tl_namespace = '10' AND tl_title IN ('B','E') ))  ORDER BY tl_from, tl_title LIMIT 501;
+------+-------------+---------------+-------+---------------+---------+---------+------+------+--------------------------+
| id   | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+------+-------------+---------------+-------+---------------+---------+---------+------+------+--------------------------+
|    1 | SIMPLE      | templatelinks | range | tl_from       | tl_from | 265     | NULL |    4 | Using where; Using index |
+------+-------------+---------------+-------+---------------+---------+---------+------+------+--------------------------+

mysql:wikiadmin@db1088 [frwiki]> explain SELECT tl_from AS `pl_from`,tl_namespace AS `pl_namespace`,tl_title AS `pl_title`  FROM `templatelinks` FORCE INDEX (tl_from) WHERE tl_from IN ('10635368', '10635369')  AND ((tl_namespace = '10' AND tl_title IN ('B','E') ) OR (tl_namespace = '11' AND tl_title IN ('B','E')))  ORDER BY tl_from, tl_namespace, tl_title LIMIT 501;
+------+-------------+---------------+-------+---------------+---------+---------+------+------+--------------------------+
| id   | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+------+-------------+---------------+-------+---------------+---------+---------+------+------+--------------------------+
|    1 | SIMPLE      | templatelinks | range | tl_from       | tl_from | 265     | NULL |    8 | Using where; Using index |
+------+-------------+---------------+-------+---------------+---------+---------+------+------+--------------------------+

The query without forcing the index:

+------+-------------+---------------+-------+----------------------+--------------+---------+------+------+------------------------------------------+
| id   | select_type | table         | type  | possible_keys        | key          | key_len | ref  | rows | Extra                                    |
+------+-------------+---------------+-------+----------------------+--------------+---------+------+------+------------------------------------------+
|    1 | SIMPLE      | templatelinks | range | tl_from,tl_namespace | tl_namespace | 265     | NULL |  124 | Using where; Using index; Using filesort |
+------+-------------+---------------+-------+----------------------+--------------+---------+------+------+------------------------------------------+

Unfortunately, it does still insist on making the filesorting index choice without the forcing.

mysql:wikiadmin@db1088 [frwiki]> explain SELECT tl_from AS `pl_from`,tl_namespace AS `pl_namespace`,tl_title AS `pl_title`  FROM `templatelinks` WHERE tl_from IN (/* 5000 page_ids */)  AND ((tl_namespace = '10' AND tl_title IN ('B','E') ))  ORDER BY tl_from, tl_title LIMIT 501;
+------+-------------+---------------+-------+----------------------+--------------+---------+------+-------+------------------------------------------+
| id   | select_type | table         | type  | possible_keys        | key          | key_len | ref  | rows  | Extra                                    |
+------+-------------+---------------+-------+----------------------+--------------+---------+------+-------+------------------------------------------+
|    1 | SIMPLE      | templatelinks | range | tl_from,tl_namespace | tl_namespace | 265     | NULL | 10000 | Using where; Using index; Using filesort |
+------+-------------+---------------+-------+----------------------+--------------+---------+------+-------+------------------------------------------+

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 10000 |
| Handler_read_last          | 0     |
| Handler_read_next          | 5544  |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+

We might be looking at different queries, this is what I am looking at:

https://phabricator.wikimedia.org/P5254
I get the same plan with and without the force

This table (T162774#3175223) is very cool, we should put it on mediawiki.org. I will expand it with use and ignore uses.

I can reproduce Anomie's issue above, and as you know this depends highly on how updated the internal stats are and the data distribution, I would compromise on the IGNORE now and then test/work towards an upgrade/total hint delete later. Whatever it takes to unblock the primary key addition.

In T162774#3175397, @jcrespo wrote:

I can reproduce Anomie's issue above, and as you know this depends highly on how updated the internal stats are and the data distribution, I would compromise on the IGNORE now and then test/work towards an upgrade/total hint delete later. Whatever it takes to unblock the primary key addition.

Fine by me!

In T162774#3175389, @jcrespo wrote:

For now, this is exactly the reason why I prefer IGNORE over FORCE (despite hating both). It is slightly more maintainable:

How so? If the index doesn't exist, IGNORE will still complain about it.

mysql:wikiadmin@db1088 [frwiki]> SELECT tl_from FROM templatelinks IGNORE INDEX (tl_does_not_exist) WHERE tl_from=123 ORDER BY tl_from, tl_title LIMIT 501;
ERROR 1176 (42000): Key 'tl_does_not_exist' doesn't exist in table 'templatelinks'

In this particular case it happens to work out because we're not renaming tl_namespace. But if something were ignoring tl_from it'd be blocking your task just like this forcing of tl_from does.

How so? If the index doesn't exist, IGNORE will still complain about it.

In this particular case it happens to work out because we're not renaming

You answered yourself :-) Because this particular case, which is not *that* uncommon.

You're saying it's more common to rename indexes that are used in FORCE INDEX than it is to rename indexes that are used in IGNORE INDEX?

You're saying it's more common to rename indexes that are used in FORCE INDEX than it is to rename indexes that are used in IGNORE INDEX?

He he, no, I am saying it is less impacting. One is saying "the index you are chosen is wrong, chose another", but still leaves room when the structure changes for mysql to be intelligent. FORCE tells to use the index no matter what, even to create poorer plans. Banning 1 index is for me safer than banning all but one. All those are technically bugs that should be solved, not converting mysql into a declrative language.

If we were going to rename tl_namespace, I would say the opposite in this case (let's use FORCE), of course. What I meant is that IGNORE working is not that uncommon (1st or 2nd option is more common than >= 3th option).

A primary key is being created on templatelinks and pagelinks (T164185)- once it is done (and merged the same change on mediawiki) we can convert the force into an ignore (so that it works on both states), and then drop the secondary index.

Change 347850 merged by MaxSem:
[mediawiki/core@master] ApiQueryLinks: Remove index forcing

https://gerrit.wikimedia.org/r/347850