Solved Incredibly poor performance for Latest Activity feed for MariaDb 10.4+

[Xon]

This is highly dependant on the user, number of followers and the site's activity. ~19.4 seconds vs 0.001 seconds is a serious performance regression.

analyze is like explain but executes the query and reports back actual row counts.

SQL:

set join_cache_level=2;
analyze
SELECT `xf_news_feed`.*, `xf_user_User_1`.*
FROM `xf_news_feed` FORCE INDEX (`event_date`)
LEFT JOIN `xf_user` AS `xf_user_User_1` ON (`xf_user_User_1`.`user_id` = `xf_news_feed`.`user_id`)
LEFT JOIN...

Read more

Continue reading...