Page MenuHomePhabricator

FORCE INDEX on ApiQueryLinks on templatelinks and pagelinks
Closed, ResolvedPublic

Description

Hello,

When working on T17441 we decided to start converting UNIQUE keys into PK as those are easy should be fine to convert. We converted a bunch of tables, including pagelinks and template links. Only one slave per shard to see how it would do. So this is how the shard is (note the UNIQUE KEY):

root@PRODUCTION s6[frwiki]> select @@hostname;
+------------+
| @@hostname |
+------------+
| db1088     |
+------------+
1 row in set (0.00 sec)

root@PRODUCTION s6[frwiki]> show create table templatelinks\G show create table pagelinks\G
*************************** 1. row ***************************
       Table: templatelinks
Create Table: CREATE TABLE `templatelinks` (
  `tl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `tl_namespace` int(11) NOT NULL DEFAULT '0',
  `tl_title` varbinary(255) NOT NULL DEFAULT '',
  `tl_from_namespace` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `tl_from` (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.00 sec)

*************************** 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',
  UNIQUE KEY `pl_from` (`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 in set (0.00 sec)

And this is how we left db1093 (only that host) (note the PRIMARY KEY - also, note that the tl_from and pl_from indexes were added yesterday to fix the issue described below)

root@db1093[frwiki]> select @@hostname;
+------------+
| @@hostname |
+------------+
| db1093     |
+------------+
1 row in set (0.01 sec)

root@db1093[frwiki]> show create table templatelinks\G show create table pagelinks\G
*************************** 1. row ***************************
       Table: templatelinks
Create Table: CREATE TABLE `templatelinks` (
  `tl_from` int(8) unsigned NOT NULL DEFAULT '0',
  `tl_namespace` int(11) NOT NULL DEFAULT '0',
  `tl_title` varbinary(255) NOT NULL DEFAULT '',
  `tl_from_namespace` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_from` (`tl_from`,`tl_namespace`,`tl_title`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.00 sec)

*************************** 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`),
  KEY `pl_from` (`pl_from`,`pl_namespace`,`pl_title`)
) ENGINE=InnoDB DEFAULT CHARSET=binary
1 row in set (0.00 sec)

We converted one slave per host, and we have seen that there were some issues with db1093 (s6, frwiki,jawiki,ruwiki) with two specific queries, which were doing a FORCE index:

pagelinks table

ApiQueryLinks::run	10.64.48.152	1176	Key 'pl_from' doesn't exist in table 'pagelinks' (10.64.48.152)	SELECT  pl_from,pl_namespace,pl_title  FROM `pagelinks`  FORCE INDEX (pl_from)  WHERE pl_from = 'xx' AND pl_namespace = '0'  ORDER BY pl_title LIMIT 501

And templatelinks table

ApiQueryLinks::run	10.64.48.152	1176	Key 'tl_from' doesn't exist in table 'templatelinks' (10.64.48.152)	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 (xxx)  AND ((tl_namespace = 'xx' AND tl_title IN ('xx') ))  ORDER BY tl_from,tl_namespace,tl_title LIMIT 501

The way we fixed this was to create an index with that same name (and with the columns it originally had) as specified above.
However, the ideal fix would be to remove that FORCE index there and let MySQL use the correct index for it.
I found this (from 2008) which might be related to why the FORCE is being used there: https://phabricator.wikimedia.org/rMWba0f2974df812bc6e1f9e7972b616ed5b9d689f0#6d708370 and https://phabricator.wikimedia.org/rMWe26e924557e591359a633c0f063bdc7f35e0bc34

Almost 10 years have passed by so maybe the error that was found at the time is solved already with all the improvements made to the optimizer. Otherwise it is going to be difficult to get rid of UNIQUE keys that are being used because of those FORCE

Thanks

Note: If the tags I have used, specially MediaWiki-API isn't correct, please feel free to change them or let me know

Related Objects

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptApr 12 2017, 7:53 AM

The MediaWiki-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:

Index forcedClassLikely to be made a PK?Refs
image.img_usertext_timestampApiQueryAllImagesNo529db96c, T29202
image.img_user_timestampApiQueryAllImagesNoLater follow-on from the above
image.img_timestampApiQueryAllImagesNo529db96c, T29202
pl_namespaceApiQueryAllLinksNo9afb42f9
tl_namespaceApiQueryAllLinksNoLater follow-on from the above
name_titleApiQueryAllPagesNo972b72f8
page.PRIMARYApiQueryBacklinksAlready is24a930a1, this comment
pagelinks.pl_namespaceApiQueryBacklinkspropNoT139056
pagelinks.pl_backlinks_namespaceApiQueryBacklinkspropNoT139056
templatelinks.tl_namespaceApiQueryBacklinkspropNoT139056
templatelinks.tl_backlinks_namespaceApiQueryBacklinkspropNoT139056
imagelinks.il_toApiQueryBacklinkspropNoT139056
imagelinks.il_backlinks_namespaceApiQueryBacklinkspropNoT139056
cl_timestampApiQueryCategoryMembersNo6f82fb14, d6c5ef7a
cl_sortkeyApiQueryCategoryMembersNoe58e384e, d6c5ef7a
archive.ar_usertext_timestampApiQueryDeletedrevsNofde54a83
archive.name_title_timestampApiQueryDeletedrevsNofde54a83
el_indexApiQueryExtLinksUsageNoc3fb81f2, planned to be dropped in Gerrit change 322729
recentchanges.rc_user_textApiQueryContributionsNoefad3a8b

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

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

Anomie moved this task from Unsorted to Needs Review on the MediaWiki-API board.Apr 12 2017, 2:15 PM
Anomie claimed this task.

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!

+------+-------------+---------------+-------+---------------+---------+---------+------+------+------------------------------------------+
| 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)
+------+-------------+---------------+-------+---------------+---------+---------+------+------+------------------------------------------+
| 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.

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!

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.

Reedy closed this task as Resolved.Aug 29 2017, 9:44 PM
Reedy added a subscriber: Reedy.

Tentatively closing this task as T172514 is now resolved

https://github.com/wikimedia/mediawiki/commit/0f13fff1604778397c4ca3072ab80ef113c936ff#diff-6d7083709b60c942ffb803711fe83615R140

$this->addOption( 'USE INDEX', [ $this->table => 'PRIMARY' ] );

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

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