+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Support (https://forum.kodi.tv/forumdisplay.php?fid=33)
+--- Forum: General Support (https://forum.kodi.tv/forumdisplay.php?fid=111)
+---- Forum: OS independent / Other (https://forum.kodi.tv/forumdisplay.php?fid=228)
+---- Thread: v18 MySQL slow requests on tv show seasons (/showthread.php?tid=340340)
RE: MySQL slow requests on tv show seasons - blodan - 2021-06-08
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;
sql:ALTER TABLE `episode` ADD INDEX( `c12`, `idShow`);
RE: MySQL slow requests on tv show seasons - fnord12 - 2021-06-09
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.
RE: MySQL slow requests on tv show seasons - blodan - 2021-06-10
(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.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;
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.
Quote:HAVING episodes > 0
RE: MySQL slow requests on tv show seasons - fnord12 - 2021-06-11
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).
RE: MySQL slow requests on tv show seasons - kossatzd - 2021-11-02
any chance the sql can be put somewhere else? the links above seem to be expired. I am facing the same problem.
RE: MySQL slow requests on tv show seasons - Klojum - 2021-11-02
@fnord12 FYI, Kodi's paste service retains pastes for some 60 days only. ^^
RE: MySQL slow requests on tv show seasons - fnord12 - 2022-01-06
@kossatzd
Sorry for the delay in responding. I got an error message when trying to post SQL code directly in the forum, so i've placed it on my github page:
https://github.com/fnord12/season_view/blob/main/sql
Let me know if I can help further.
RE: MySQL slow requests on tv show seasons - john_es - 2022-03-30
Using your query, I am getting:
Operation failed: There was an error while applying the SQL script to the database:
ERROR 1054: Unknown column 'episodes' in 'where clause'
It's weird because it is defined in the code on github. Do you have a copy of your seasons_view you could share?
RE: MySQL slow requests on tv show seasons - SakuraKira - 2022-03-31
Query is not working. I fiddled out the errors of „spaces“ but it still throws an error.
I had fixed the database this way on MySQL but now I don’t know anymore how the syntax was.
I have a lot of movies and TVShows (looks like more others in this thread have) so my „problem“ is quite extreme
RE: MySQL slow requests on tv show seasons - fnord12 - 2022-03-31
Blodan's original code (see thread above) used "HAVING episodes" instead of "WHERE episodes" so if you are using MYSQL instead of SQLite you may try that (although as you point out, the error message you're getting doesn't fit). My code works on Kodi 18 / MyVideos116.db with SQLite.
RE: MySQL slow requests on tv show seasons - SakuraKira - 2022-03-31
The code posted by you an blodan in the pastebin is gone since pastebin has only some days retention.
The code on github does not work (even when fixing the obvious bugs with missing spaces).
I am using mariaDB btw
I can see when querying the View via HeidiSQL it takes forever which is the result of over 1000 series with multiple Episodes. Some even with 100 and more. I think there is a possibility to make the query a lot faster.
Same for tvshowview which is referenced in it.
My machine is not the problem (Xeon 8core with 64gb RAM and System on SSD)
I am not capable enough in SQL to see how to speed up the views
RE: MySQL slow requests on tv show seasons - SakuraKira - 2022-04-02
@blodan can you provide your sql statement again?
RE: MySQL slow requests on tv show seasons - Klojum - 2022-04-02
(2022-04-02, 22:13)SakuraKira Wrote: can you provide your sql statement again?
Doubtful... Mr blodan last visited the Kodi forum on 18-07-2021.