Could be related to the pagelinks schema migration T351237: Set beta and production to read new for pagelinks migration / T352010: Gradually drop old pagelinks columns

Yes. It's combination of pagelinks migration and mariadb hating us.

wikiadmin2023@10.192.0.223(dewiki)> explain SELECT  lt_title AS `pl_title`,pl_from  FROM `pagelinks` JOIN `linktarget` ON ((pl_target_id=lt_id))   WHERE lt_namespace = 0  ORDER BY lt_title,pl_from LIMIT 11  ;
+------+-------------+------------+------+----------------------------+--------------------+---------+-------------------------+---------+----------------------------------------------+
| id   | select_type | table      | type | possible_keys              | key                | key_len | ref                     | rows    | Extra                                        |
+------+-------------+------------+------+----------------------------+--------------------+---------+-------------------------+---------+----------------------------------------------+
|    1 | SIMPLE      | linktarget | ref  | PRIMARY,lt_namespace_title | lt_namespace_title | 4       | const                   | 7497146 | Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | pagelinks  | ref  | pl_target_id               | pl_target_id       | 8       | dewiki.linktarget.lt_id | 7       | Using index                                  |
+------+-------------+------------+------+----------------------------+--------------------+---------+-------------------------+---------+----------------------------------------------+
2 rows in set (0.001 sec)

At least the unique mode seems fixable

current query, explain shows Using temporary:

SELECT DISTINCT lt_title AS `pl_title`  FROM `pagelinks` JOIN `linktarget` ON ((pl_target_id=lt_id))   WHERE lt_namespace = 0  ORDER BY lt_title LIMIT 11

Suggestion (no temp table in explain)

SELECT lt_title AS `pl_title` FROM `linktarget` LEFT JOIN `pagelinks` ON ((lt_id=pl_target_id)) WHERE pl_target_id IS NOT NULL AND lt_namespace = 0  ORDER BY lt_title LIMIT 11

The join is needed to know if the linktarget is a link or a template, but it is enough to find one row in the pagelinks table to say so, so a left join could fit this.

There should be some way simpler to fix this. Is there a reason the order is not on pl_from, lt_namespace, lt_title?

Removing pl_from from the order fixes the issue since the order columns will be on one table only.

This would fix it:

SELECT  lt_title AS `pl_title`,pl_from  FROM `pagelinks` STRAIGHT_JOIN `linktarget` ON ((pl_target_id=lt_id))   WHERE lt_namespace = 0  ORDER BY pl_target_id,pl_from LIMIT 11;

I'll check how hard it is to make a patch.

Change 1012444 had a related patch set uploaded (by Ladsgroup; author: Amir Sarabadani):

[mediawiki/core@master] api: Make changes after pagelinks migration

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

Change 1012444 merged by jenkins-bot:

[mediawiki/core@master] api: Make changes after pagelinks migration

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

Thank you @Ladsgroup for the patch. The original links given with this task works now but the requests still fails if alunique parameter is given like:
https://de.wikipedia.org/w/api.php?action=query&list=alllinks&alunique=&alfrom=B&format=jsonfm
resulting in

{
    "error": {
        "code": "internal_api_error_Wikimedia\\RequestTimeout\\RequestTimeoutException",
        "info": "[4ba38f41-bf63-42af-895e-1650ed00046a] Caught exception of type Wikimedia\\RequestTimeout\\RequestTimeoutException",
        "errorclass": "Wikimedia\\RequestTimeout\\RequestTimeoutException"
    },
    "servedby": "mw-api-ext.eqiad.main-5cb9485489-fwnz6"
}

A similar response comes with es.wikipedia.org. I didn't made other tests.

Now it fails for en-wiki too. zh-wikisource is still working, I guess it resides on s2 which is not migrated yet.

yeah, I'm honestly inclined to move that to behind misermode (=disabling it in production). Going through every link in a wiki doesn't make much sense, doesn't provide any benefit.

Also using alprefix needs a lot of time and fails for wikidata:
https://www.wikidata.org/w/api.php?action=query&alnamespace=0&alprefix=Fix&format=jsonfm&list=alllinks

{
    "error": {
        "code": "internal_api_error_Wikimedia\\RequestTimeout\\RequestTimeoutException",
        "info": "[6dcabee7-8010-48c1-8bf1-58b90f76318a] Caught exception of type Wikimedia\\RequestTimeout\\RequestTimeoutException",
        "errorclass": "Wikimedia\\RequestTimeout\\RequestTimeoutException"
    },
    "servedby": "mw1402"
}

https://en.wikipedia.org/w/api.php?action=query&alnamespace=0&alprefix=Fix&format=jsonfm&list=alllinks
needs ~ 25 seconds for response

There is also a lot of time needed on wikidata with namespace parameter different from 0:
https://www.wikidata.org/w/api.php?action=query&alnamespace=1&format=jsonfm&list=alllinks
Sometime it also results in a timeout.

All these issues can be found with Pywikibot tests e.g.
pwb -site:wikidata site_generators_tests -v TestSiteGenerators.test_all_links

Change #1022453 had a related patch set uploaded (by Xqt; author: Xqt):

[pywikibot/core@master] [bugfix] circumvent problems with unique and prefix parameters (T359425)

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

Change #1022453 merged by jenkins-bot:

[pywikibot/core@master] [bugfix] circumvent problems with unique and prefix parameters (T359425)

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

It's not a bug of the optimizer.

Change #1031086 had a related patch set uploaded (by Umherirrender; author: Umherirrender):

[mediawiki/core@master] api: Improve query for list=alllinks with alunique=

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

@Umherirrender I hope to actually deprecate the endpoint: T364617: Deprecate and remove alllinks API endpoint

Change #1031556 had a related patch set uploaded (by Umherirrender; author: Umherirrender):

[mediawiki/core@master] api: Fix list=alllinks for descending order

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

In T359425#9794870, @Ladsgroup wrote:

@Umherirrender I hope to actually deprecate the endpoint: T364617: Deprecate and remove alllinks API endpoint

Getting slow-query warnings from deprecated modules is not useful, so it should be fixed. Also for backporting to REL1_42, where it seems broken as well, but is not deprecated.

Change #1031086 abandoned by Umherirrender:

[mediawiki/core@master] api: Improve query for list=alllinks with alunique=

Reason:

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

Change #1031556 abandoned by Umherirrender:

[mediawiki/core@master] api: Fix list=alllinks for descending order

Reason:

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