Hi,

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: https://www.percona.com/blog/2011/10/13/when-explain-estimates-can-go-wrong/ 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 fresh session: https://www.slideshare.net/jynus/query-optimization-with-mysql-80-and-mariad...

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@gmail.com wrote:

Hi all,

The execution plan for this query [1] 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 [2] 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?

Thanks!

Huji

[1] https://tools.wmflabs.org/sql-optimizer?use=enwiki_p&sql=use+fawiki_p%3B...

[2] https://tools.wmflabs.org/sql-optimizer?use=fawiki_p&sql=select%0D%0A++p... _______________________________________________ Wikimedia Cloud Services mailing list Cloud@lists.wikimedia.org (formerly labs-l@lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/cloud