[Ballistic]

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.

Open up phpmyadmin and lookie here:
http://www.server-works.com/innodb.png

Sofar i have not gotten any further with this issue. Movies/TV-Shows etc all load up reasonably fast concidering i have 4500+ movies and 200shows/15k episodes.
The main problem has not changed. Which is the loading time of the main menu after startup. Kodi grabs the entire db instead of limiting it to the latest 25 results.

Until the developers add a function to change this, i will still be running on XBMC 13.2 unfortunately.

[Ballistic]

I solved this problem by moving to a smart-playlist friendly skin. Arctic zephyr to be precise.
This skin makes it very easy to create smart playlists that you can apply as widget. In these smart playlists which you can basically change the mysql query. This way i limited it to 10 results which gives lightning fast results.

[stellenbosser]

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!!!!

[Memphiz]

Finally someone who realised that kodi was created by a bunch of internetional idiots. There is light at the end of the tunnel now that we will see some proper, sane and obvious correct performance fixes from our new and first "real good programmer" named stellenbosser. Its so good to have you on board. Looking forward to finally learn something from you.

[cyber7]

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!!!!

Hi stellenbosser! I am from Durbanville, so always good to see/hear someone local. I have a different problem and that is the indexing of movies in Kodi 17 is soooo slow, it makes for a real bad experience.

Unfortunately I have a toddler (21 months) and another about to be born, but will start using your post as a pointer to get this mysql beast debuged. It seems you are going to assist with the coding, if so, I will let you know my findings. (+10,000 episodes and +8,000 movies)

cheers
cyber7
(www.cyber7.co.za)

[Ou812_2018]

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 . 

I set the procedures and triggers up as posted in 18.3 as I too have a rather large collection of shows. It made a huge speed difference but it seems to have broke some other items. The playCount and lastPlayed columns no longer update in files when I watch an episode. Also, the scrapper seems to be experiencing scapping issues. It tells me for each file I import that the information is unavailable. If I switch the tables back to being views and drop the functions and stored procedures, this start working again at a cost of very slow menu loads. Is there an update to this issue? I know this thread has aged a bit since it was first posted.