(2015-05-22, 09:46)Milhouse Wrote:

(2015-05-22, 03:55)telsin Wrote: So I recommend double checking your tables to make sure they are InnoDB too, not just the database.

What would be really useful is if you explained what ordinary users are supposed to check, and if necessary, change.

(2016-11-15, 17:17)stellenbosser Wrote: I'm afraid you are looking in the wrong place. You can do all you want to make MySQL faster, but it is not going to solve anything. I have a low spec MySQL server (Basic QNAP fileserver), and I use a Raspberry PI as Kodi client using MySQL. I added a smart playlist with 'afrikaans' in the Genre. Once the smart playlist was created, I pressed 'q' to while the playlist title was selected. It took between 20 and 30 minutes (not SECONDS - MINUTES) before the first song started playing. Enough time for me to log into Kodi Raspberry PI via ssh and to see what was going on. The processor was running at about 2.5% and kodi was only using 19% of RAM - in other words it was idling all the way. So I installed a MySQL client (command line) on the RPI. To get a list of the songs I was looking for could be retrieved with a simple SQL statement, so I executed "SELECT * FROM song WHERE lower(strGenres) LIKE '%afrikaans%'. 0.7seconds (Yes - less than 1 second) later I had information of all of the 1081 songs transferred to my Kodi RPI via my GB LAN connection. All this basically means is that whoever wrote the MySQL interface (or the framework for getting playlist information for that matter) basically does not know anything about programming. If it was developed properly you would have had your playlist ready and sorted in the sequence you preferred in less than 2 seconds. Ok, so there is a bad design in Kodi, but even with that there are ways to have kodi perform as if the playlist was always immediate. If you have a song, start playing it (in stead of locking the entire frontend - yet another BAD design - threads allows a good programmer to have a responsive frontend while some heavy lifting is happening where no one is aware of it) - there is no point in waiting till you have all 1000 items loaded if you tortoises are laughing at you. If you get the first song, PLAY it. If you managed to load another 20 songs by the time song 1 is at its end, then make a better decision on which one to play next. But really - the infrastructure needs attention!!!!

(2015-03-27, 02:03)bambi73 Wrote: 000+ TVShows and 70000+ episodes in library. Nothing much what I can't do about it because I want it here . Unfortunately Kodi isn't optimized for such amounts.
SQL Views: In Kodi they aren't really complex but they aren't simple either. But together with higher amount of data they start to be quite slow. In my case tvshowview is ~750ms and seasonview is ~1500ms, other (episodeview and tvshowcounts) are passable. My solution for this problem was to make them tables (with same structure) filled by triggers instead of views. It returns DB response time into tens ms territory and is completely transparent to Kodi. You can check my triggers at GitHub. Feel free to use them if you know what you are doing, but it's on your own risk as I don't intend to support it .
Kodi code itself: Even though SQL View optimization helps quite a bit, main problem still remains in application itself. Unfortunately Kodi load TV Show list too often, even when nothing changed it does full roundtrip to DB together with filtering, sorting, looking up current item etc. It works fine with regular library size but response times start to skyrockets with increasing amount of shows. There is file cache implementation but it kick in when response time is over 1s, which is already past usable response. Last year I spent some time to "optimize" code and you can check results at my GitHub repo. But please be warned, these optimizations are quite rough as I'm not regular C/C++ developers and I'm almost sure that I cut some corners more than it's healthy. It works fine for my use-cases, but ... Again feel free to use it at your own risk .