I have not checked the actual queries, but EXPLAIN in old versions of
MySQL did not give sometimes accurate results (specially related to
subqueries). This is an old but illustrative example of it:
Sometimes running ANALYZE on the tables involved can recalculate
statistics and provide better results (I can do that if I get pinged
about it, like here: https://phabricator.wikimedia.org/T224656
Sometimes there is an optimizer bug and we need to upgrade to overcome
it. Sometimes we are personally dissatisfied with the lack of response
on our optimizer bug reports on MariaDB tracker. In this aspect I
personally think MariaDB used to be ahead, but since 5.6 and beyond,
MySQL has generally done a better job in some cases.
Rewriting queries is sometimes the only solution. To test performance
of query plans post-execution, Handler statistics can be used on a
Regarding your last question- *links tables and other similar data
(certain counters) are non-canonical data, which are generally
calculated at some point after edits. There can be some delay on it to
be updated, and in some exceptional cases (e.g. temporary sw or hw
error), they can be out of sync from the actual edit due to
postprocessing errors. They cannot be in sync because if one does an
edit and then it has to count millions of members of a
category/wikilnks, an edit would take minutes to be processed. A null
edit or sometimes some production maintenance can lead to getting the
revision data and the other metadata in sync.
On Wed, Jun 26, 2019 at 3:42 AM Huji Lee <huji.huji(a)gmail.com> wrote:
The execution plan for this query  indicates that it will be entirely based on
existing indexes. Yet in practice, the query takes a long time to run. Why is that?
Also, interestingly, the execution plan for this modified version  is identical, but
it runs almost instantaneously.
Lastly, I expect that last query to return no results (because the NOT EXISTS condition
should not be met) but it does return a row. Why is that?
Wikimedia Cloud Services mailing list
Cloud(a)lists.wikimedia.org (formerly labs-l(a)lists.wikimedia.org)