This is from earlier work on this https://github.com/civicrm/civicrm-core/pull/12810

The short answer is that it's using an index_merge or index_intersect instead of the contact_id key - will flesh out more soon - here are some links

https://www.percona.com/blog/2012/12/14/the-optimization-that-often-isnt-index-merge-intersection/
http://gobitcan.com/blog/2014-12-03-what-to-do-when-mysql-ignores-your-index
http://mysqlopt.blogspot.com/2013/05/mysql-slow-query-example-of-index-merge.html
https://www.percona.com/blog/2012/12/14/the-optimization-that-often-isnt-index-merge-intersection/
https://stackoverflow.com/questions/16283472/why-is-mysql-showing-index-merge-on-this-query
https://mariadb.com/kb/en/library/fair-choice-between-range-and-index_merge-optimizations/
https://dba.stackexchange.com/questions/178286/mysql-on-rds-avoiding-index-merge
https://mariadb.com/kb/en/library/index_merge-sort_intersection/
https://stackoverflow.com/questions/47943755/mysql-optimizing-subquery-with-index-merge
https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html

On staging I got a 50% speed improvement on loading the contribution tab for Anonymous Contact (and other slow contacts) by simply removing some redundant query calls

We've deployed removing the redundant calls (noted above) - I'm trying to fix the query so it doesn't use an index merge. However, there is some cruft/nastiness in the query to clean up first - I have added https://github.com/civicrm/civicrm-core/pull/13319 to address that & will try to get relevant upstream review

https://github.com/civicrm/civicrm-core/pull/13512 merged upstream

Change 491383 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/crm/civicrm@master] Remove mode & median calculations

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

The patch I just added shaves 24 seconds off the contribution tab load time. I think when we started it was over a minute - this goes from 33 seconds to 9 to load the contribution listing

after

Search for Benevity in Jan (no rows returned as none on staging)
Before 1 min 24
After 10 seconds

Checks in Jan (staging data, only 2k)
before 1 min 30 sec
After 1min 8sec

Change 491383 merged by jenkins-bot:
[wikimedia/fundraising/crm/civicrm@master] Remove mode & median calculations

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

@MBeat33 @NNichols @LeanneS I deployed a patch today that takes 20 sec off the time to load the contribution tab for anonymous contact and 1 minute off some contribution searches - hopefully you / engage notice the difference :-)