Logo of SermonSpeaker Extension Claim

Login Form

Counting of sermons for series very slow

More
3 months 2 weeks ago #7099 by Ken Lee
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
Attachments:

Please Log in or Create an account to join the conversation.

More
3 months 2 weeks ago #7100 by Ken Lee
I undo the lines at series.php and speakers.php in models.
github.com/Bakual/SermonSpeaker/commit/b...a854bc930562d66d1a47
Now they are blazing fast.

Please Log in or Create an account to join the conversation.

More
3 months 2 weeks ago #7101 by Thomas Hunziker
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.

Feel free to rate SermonSpeaker on JED .
Useful documentation for beginners: First Steps & FAQ
How to say "Thank You!": Contribute or support using the Donate button at the bottom of each page.

Please Log in or Create an account to join the conversation.

More
3 months 2 weeks ago #7102 by Ken Lee
Email sent. Please check.

Please Log in or Create an account to join the conversation.

More
3 months 1 week ago #7103 by Thomas Hunziker
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?

File Attachment:

File Name: com_sermon...5-07.zip
File Size:4,036 KB


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.

Feel free to rate SermonSpeaker on JED .
Useful documentation for beginners: First Steps & FAQ
How to say "Thank You!": Contribute or support using the Donate button at the bottom of each page.
Attachments:
The following user(s) said Thank You: Ken Lee

Please Log in or Create an account to join the conversation.

More
3 months 1 week ago #7104 by Ken Lee
Installed the attached version. It is really fast now.

Please Log in or Create an account to join the conversation.

Time to create page: 0.101 seconds
Powered by Kunena Forum