I can confirm that at some point you hit a threshold and that the script is a quick fix at the only location that one can touch without too much involvent with Kodi itself.
My library currently holds about 120 tv shows and about 10k episodes and the script not only improved keeping the data consistent but also improved the navigation speed within the menus.
So I’ll monitor this thread, patiently waiting for the update 
Only relevant changes seem to be the addition of two columns to some of the views:
If someone wants to test it (backup first!) https://gist.github.com/Scythe42/1306ba243ce13d6384eb203d94855670
Feedback, bug reports are welcome of course. Still, it’s a quick and dirty hack without having to fiddle with Kodi itself, that might be useful to some people.
Just one addition:
I also patched Kodi a bit to always ask my SQL Server (same as on 17): Disable Broken SQL Caching in Kodi · TwentyWasHere/osmc@5394095 · GitHub
Ist just disables that Kodi reuses what ever is memory if a query took longer than one second, which is quiet a dirty hack to begin with. Why? Because that screwed up the watched status indicator for TV shows most likely, e.g. you watch an episode and it’s wrong in season or TV show view. It just is inconsistent as this information is not properly updated in memory in general. Didn’t want to spent time to rewrite it and the I already fiddled with the DB anyway, which should be enough already.
Any magic numbers are always bad. Was too lazy to make this option configurable…
Though mileage may vary, this is aimed at low end servers like running MariaDB on a ARM based NAS with caching enabled of course (often disabled out of the box) or if you run your DB on a Pi.
If just views to tables (which increases performance on low end sytstems as the CPU doesn’t need to do much) doesn’t fix it, it could be that the above patch is also needed in case queries still takes > 1 second.
I tested your new update. Before your fix
select * from season_view where idshow = 150
took about 2s. Now it takes .0022s. Much better!
Thanks for the updated fix!
The Season View has especially horrible performance and is the one that probably benefits the most from the views to tables hack.
Let me know if you find any oddities or if I overlooked something like a missing index or so. Never bothered to go through the logs for various queries to see if an additional index is needed (I started to check but never finished, buried deep on my TODO list).
Hello together,
an update/warning/info from my side:
MAKE SURE TO READ THE COMMENT FROM @bmillham BELOW TO AVOID FIXING THE ISSUE BADLY AS I DID BELOW.
updated to Leia (MyVideo116) today and … well it failed.
The update failed, because it couldnt create the episode_view VIEW (obviously).
Therefore, Kodi didnt update the ‘version’ table to 116 and later on was unable to find the movies/episodes (i assume it does a version check regardless of the db-name).
To get it to work I did the following:
You should have done that, then did the V18 upgrade and then run the script again to install the new version.
Huh.
yeah. I did miss that.
Maybe I’ll redo it with this procedure, since i have a backup anyway.
Probably even better, because now i’ve got a an episode_view_orig TABLE instead of a VIEW.
Before running an update you need to delete the newly created tables and rename the backuped views (renamed to *_orig) to their original names. Then the upgrade should complete: Reason is that the upgrade procedure in Kodi tries to copy the content of all tables in the BD prior upgrading from the old to the new DB. Naturally it cannot do that with the newly created tables as it tries to copy the data from the v17 tables to the v18 views, which will of course fail. Therefore the upgrade never finishes. Once upgraded you can run the new version of the script and all should be good.
If you need a script for v17 or v18 to revert to the “stock” database let me know and I’ll whip something quickly up you can run. It would simply delete new tables, renames view to the original name, delete new triggers and revert changes to the already existing triggers.
If someone wants that for easy handling just ping me…
That would probably be a good idea to give people a script to revert back to original database. I know it’s not that difficult to do manually, but for someone with minimal SQL skills it would make it easier for them.
I think that would be a great idea.
I’ve done it manually now, and write it down here in case you do not find the time to make such a script:
the last two steps might not even be necessary, because the migration script from kodi is not moving triggers to the new db.
Note: if you have already updated, you can simple delete the new database and restart Kodi. It will do a database migration again.
I’m aware this is an old thread.
But I figured i share my experience with the update to v19 (Matrix) of KODI.
The script created by @TwentyWasHere for Leila seems to be still good.
No new columns in any of the 5 views that are being turned to tables (that I would have found)
only thing to change is the first Statement in the SQL file:
Instead of
USE MyVideos116
use
USE MyVideos119
I’ve also added two "LEFT JOIN"s to the “updateSeasonView” Procedure (for episodes and files) because otherwise the seasonView is only updated once there are any episodes.
At least on my side I had an issue with episodes not being added and then the season_view VIEW and the season_view TABLE did not contain the same content.