Spotted in production:
May 1 15:45:18 mw1223: SlowTimer [59999ms] at runtime/ext_mysql: slow query: SELECT /* ApiQueryAllPages::run Pywikibot-test */ /*! STRAIGHT_JOIN */ page_namespace,page_title,page_id,page_content_model,page_restrictions,page_is_redirect, page_is_new,page_touched,page_latest,page_len FROM `page`,`langlinks` WHERE page_namespace = '0' AND (page_title>='!') AND (page_id=ll_from) GROUP BY page_title,page_id ORDER BY page_title LIMIT 6
The explain for the query is deceptively simple:
Per @Anomie on IRC:
It's choosing a good index and not filesorting. The only bit that looks suspicious to me is the join with langlinks, if langlinks is mostly empty on that wiki, which would make it have to go through a lot of rows."
On a wiki like enwiki, this query is fast. On something like wikidatawiki where there's basically no langlinks in NS0, the edge case is really really slow, as in 60s or more.
An idea was floated:
No idea. Removing the straight join might work for wikidatawiki since langlinks is so small, but still wouldn't help the general case where other namespaces have lots of langlinks. Could add a "ll_from_namespace" column, I suppose.
That could work, but would require backfilling to be useful.