v18 MySQL slow requests on tv show seasons
#31
For anyone getting as frustrated as me when upgrading to 19.1 with the slow kodi season_view replace the view query with this query and you will have < 0.5s query time with mysql 5.7 without cache enabled. Not sure if it will work with kodi 18, i jumped from 17 to 19 in one go.

https://paste.kodi.tv/iyakeheyob

You also need to add another index;
ALTER TABLE `episode` ADD INDEX( `c12`, `idShow`);
Reply
#32
I tried this in 18.  It mostly works.  The seasons view is now lightning fast.  The problem is that it now always shows a Season -1 as the first season, which appears to be the "all seasons" view which I have turned off via settings.  And also every show displays the Specials season even if there are no Specials.

The other problem for me is that I'm using Kodi's default SQLite, not MySQL, and using Alter Table to create an index is only supported in MySQL.  And I don't know the syntax to create the index for SQLite.  Not sure if not having the index will hurt me in the long run.

But!  The Season view is now so fast!  Thanks so much @blodan !  Now just have to decide if I can live with the Season -1s and Specials.
Maintaining a few add-ons for v18 including PseudoTV (Classic), Tag Overview, and Autosub: https://github.com/fnord12
Reply
#33
(2021-06-09, 00:33)fnord12 Wrote: I tried this in 18.  It mostly works.  The seasons view is now lightning fast.  The problem is that it now always shows a Season -1 as the first season, which appears to be the "all seasons" view which I have turned off via settings.  And also every show displays the Specials season even if there are no Specials.

The other problem for me is that I'm using Kodi's default SQLite, not MySQL, and using Alter Table to create an index is only supported in MySQL.  And I don't know the syntax to create the index for SQLite.  Not sure if not having the index will hurt me in the long run.

But!  The Season view is now so fast!  Thanks so much @blodan !  Now just have to decide if I can live with the Season -1s and Specials.
Thats a easy fix, I noticed it myself, just add this as the last row in the query and it will work as the original query and still being fast;
Quote:HAVING episodes > 0 
Reply
#34
SQLite didn't allow HAVING but changing it to WHERE did the trick.  Now it's still super-fast but without the extraneous seasons!  Thanks again!

I also found that there was a problem with the Watched checkmarks.  It seems that if you'd watched all episodes in a season but also watched an episode more than once, the Watched checkmark was not appearing.  Changing your query for the playcount from a SUM to a COUNT seems to have fixed the problem (which is a little weird but I'm not complaining; I'll update if that causes some other problem).

So, for me (in v18 and with SQLite ) here's the final statement that worked for me:

https://paste.kodi.tv/ibovoyejer

Thanks again @blodan ! This solved a major annoyance for me (and hopefully a lot of others that have run into this).
Maintaining a few add-ons for v18 including PseudoTV (Classic), Tag Overview, and Autosub: https://github.com/fnord12
Reply



Logout Mark Read Team Forum Stats Members Help
MySQL slow requests on tv show seasons0
This forum uses Lukasz Tkacz MyBB addons.