(2015-12-23 19:54)brando56894 Wrote: I'm getting notices in PhpMyAdmin that I need to add indexes, but I'm not too proficient with SQL. What columns would be best to index in which tables? I mainly watch TV shows and I have a very large database (7500 shows), while looking at the WatchedList database, the Episode_Watched table has 4227 rows, so I'm assuming this would be the best table to index, but I'm not sure which columns would be the best. My database is running off of my FreeNAS box (Intel 8 core Atom @ 2.4 GHz, 32 GB RAM, Samsung 850 Evo SSD) and when I run the plugin on my Nvidia Shield TV or my Windows 8 PC, it runs through the whole scanning process pretty quickly (30-60 seconds), but PhpMyAdmin makes me believe it could be faster.
From my understanding of mySQL, the index should be the column that is preferably unique and is used in WHERE clauses often.
So the best indexes for the WatchedList tables would be the columns already marked as primary:
ALTER TABLE `tvshows` ADD INDEX(`idShow`);
ALTER TABLE `episode_watched` ADD INDEX( `idShow`, `season`, `episode`);
ALTER TABLE `movie_watched` ADD INDEX(`idMovieImdb`);
(2015-12-23 19:54)brando56894 Wrote: These are the notices I'm getting:
- There are too many joins without indexes. This means that joins are doing full table scans. Adding indexes for the columns being used in the join conditions will greatly speed up table joins
- The rate of reading data from a fixed position is high. This indicates that many queries need to sort results and/or do a full table scan, including join queries that do not use indexes. Add indexes where applicable.
- The rate of reading the next table row is high. This indicates that many queries are doing full table scans. Add indexes where applicable.
From the design of the addon, the full tables for movies, tvshows and episodes have to be read at startup to be compared against the tables from Kodi.
The only thing that should be improved by the indexes is the updating of the watched state and the synchronization of the tv shows list:
UPDATE movie_watched SET ... WHERE idMovieImdb LIKE ...
UPDATE episode_watched SET ... WHERE idShow LIKE %s AND season LIKE %s AND episode LIKE %s
INSERT IGNORE INTO tvshows (idShow,title) VALUES (%s, %s)
I tested using the addon with my configuration (58 tv shows, 3684 episodes, 799 movies) and it took 7 seconds with and without indexes (only reading, no updating).
Do you get improvements when you apply the table alterations above?
(2015-12-23 19:54)brando56894 Wrote: Here's my query list after 12 days usage
Is your Kodi video library also on this mySQL server and included in these stats? I have the impression that reading the Kodi database through the JSON interface takes most of the time when running my addon.