Counting of sermons for series very slow

Dear Thomas,

At my development site I upgraded to 5.8.3 and find the the listing of Series gets very slow. It takes about a minute to show.
Attached SlowQuery.txt file is the show full processlist and explain select statement in Mysql during the wait. It seems the select count subquery is performing badly.
My site has about 40,000 sermons and 260 series.

Thank you for helping.

Regards,

Ken

I undo the lines at series.php and speakers.php in models.
github.com/Bakual/SermonSpeaker/commit/b...a854bc930562d66d1a47
Now they are blazing fast.

I take it this is in backend only, not frontend.
When I wrote that subquery, I expected a small performance impact. But nowhere like what you experience. That's a big surprise.
I would be interested to get a a dump of your sermonspeaker database tables if you're willing to share those. So I can try with a big real life sample. Otherwise I try to build an own big sample and see if something can be improved by either writing the query different or adding some indexes to the table.

Email sent. Please check.

Thanks, that was very helpful. It didn't take a minute on my localhost but still notable 15 seconds which was enough to do some tests.
Can you try with the attached version?


It does add some indexes to the database tables. Especially the one on the series_id column did bring ther query down to around 50 miliseconds. I think that's acceptable so far. I'm interested if that works the same for you.

Installed the attached version. It is really fast now.