HowTo Improve Movie & Music database speed 1,000 times

[HappyTalk]

I just migrated my MythTV video database to MediaPortal with much SQLing/Regex replacing. It had over 6,000 movie entries and media portal was taking over a minute just to load up, change genre etc = unuseable. Fortunately just adding indexes to the database made it load up pretty instantly. I DO have a lot of records 6k videos, 43k music so for me it was a must. For small db's with no speed issues it's not required, but cannot harm, as insertions appear no slower and the db's file sizes are tiny anyway. (still need to do time trial on music scan with/out indexes)

------------
Add indexes to your video database (does NOT delete data):-

1) make a copy of your current video database should you wish to revert later it's here:-
"C:\Program Files\Team MediaPortal\MediaPortal\database\VideoDatabaseV5.d b3"

2)
> create the file in code below as index_video.sql
> Run "D:\Program Files\Team MediaPortal\MediaPortal\Docs\SQLite Database Browser.exe" (or associate with files of .db3 type)
> Open up the original VideoDatabaseV5.db3 file and do 'File | Import | Database from SQL file'; Select the index_video.sql file; and then click NO at the next dialog so it DOES import into current database - That's it.

BEGIN TRANSACTION;
CREATE INDEX actorlinkmovie_idActor ON actorlinkmovie(idActor ASC);
CREATE INDEX actorlinkmovie_idMovie ON actorlinkmovie(idMovie ASC);
CREATE INDEX actors_strActor ON actors(strActor ASC);
CREATE INDEX files_idMovie ON files(idMovie ASC);
CREATE INDEX files_idPath ON files(idPath ASC);
CREATE INDEX genrelinkmovie_idGenre ON genrelinkmovie(idGenre ASC);
CREATE INDEX genrelinkmovie_idMovie ON genrelinkmovie(idMovie ASC);
CREATE INDEX movie_idPath ON movie(idPath ASC);
CREATE INDEX movieinfo_iYear ON movieinfo(iYear ASC);
CREATE INDEX movieinfo_idDirector ON movieinfo(idDirector ASC);
CREATE UNIQUE INDEX movieinfo_idMovie ON movieinfo(idMovie ASC);
CREATE INDEX movieinfo_strTitle ON movieinfo(strTitle ASC);
CREATE INDEX path_strPath ON path(strPath ASC);
COMMIT;

------------
Add indexes to music database:-
1) Save the one below as index_music.sql and similarly import into musicdatabase5.db3

BEGIN TRANSACTION; 
CREATE UNIQUE INDEX idx_artist_strArtist ON artist(strArtist ASC); 
CREATE INDEX idx_album_idArtist ON album(idArtist ASC); 
CREATE INDEX idx_album_strAlbum ON album(strAlbum ASC); 
CREATE UNIQUE INDEX idx_path_strPath ON path(strPath ASC); 
CREATE INDEX idx_song_idArtist ON song(idArtist ASC); 
CREATE INDEX idx_song_idAlbum ON song(idAlbum ASC); 
CREATE INDEX idx_song_idGenre ON song(idGenre ASC); 
CREATE INDEX idx_song_idPath ON song(idPath ASC); 
CREATE INDEX idx_song_strTitle ON song(strTitle ASC); 
CREATE INDEX idx_song_iYear ON song(iYear ASC); 
CREATE UNIQUE INDEX idx_genre_strGenre ON genre(strGenre ASC); 
COMMIT;

------------

With profiling of the database, tying the indexes used with the joins used, code efficiency it could probably be improved but both video & music with sh1tloadz of records are now fast enough to be useable so that'll do for now.

I did a test on the original database and the indexed one, the original takes about 20 seconds to execute this query, the indexed one is instant:-

SELECT movieinfo.strTitle FROM movieinfo
       JOIN genrelinkmovie ON movieinfo.idMovie = genrelinkmovie.idMovie 
       WHERE (genrelinkmovie.idGenre=104) ORDER BY movieinfo.strTitle  LIMIT 32,16;

This simulates what I guess MP is doing for the main way that 'I' use it. I would liked it to have had categories as well as genres, so for now I added them as genres starting from 101..107 (_Comedy, _Doc, _Drama, _Movie, _Music, _Scifi, _Karaoke), that explains that 104 in there. When you got 6k videos they need some categorising or it gets kinda hectic.

[ronilse]

Hi,
Have you tried with adding new entries in MP after this fix (this gives errors & a crash in MP with latest SVN)?

Regards
Roy

[HappyTalk]

Works fine this end using the latest release version = MediaPortal v0.2.0.3

I just added a dvd (containing 12 divx files) using the Movie Database editor in MP Setup and it imported fine without crashes.
The svn may have other issues causing crashes. I would try again from a blank database with the indexes added. I will try the svn myself later, when/if get time.

I will at some point remove the indexes one by one while trying diff aspects of My Videos to see which ones are key to affecting it. The indexes are in keeping with similar indexes used by MythTV So logically make sense. I think the db code maybe needs to be optimised, I'm no expert but have done a fair bit over the years.

[Maschine]

Do you see a chance to improve the other dbs also? Music and TV databases are also a bit slow sometimes when it comes to a hughe amount of data. Would be nice if you could have a look and post your results here.

Thanks
Maschine

[HappyTalk]

....

[Johnny-D]

I think the TV db is in desperate need of something too. I've been trying to import and configure the hundreds of sky channels and have come across massive performance problems.

Massive in that MP ends up using 200+Mb of RAM and then doesn't release it even when you exit the EPG, and when you shut down the GUI disappears but the task *seems* to remain in task manager indefinitely (still taking up 200Mb of RAM!).
I say seems to, because I tried with 5 and 9 channels and as soon as I got to 9, it took 10+ seconds to disappear from from Task Manager.

I may be barking up the wrong tree, but on the face of it there seems to be a database issue as the TV database swells to a size bigger than the TVguide.xml file (which implies it's imported the whole lot, and so the db is where it isn't letting it go from).

If someone could post some TV indexing SQL code I'd be happy to give it a bash.

JD

Edit: just confirmed it does actually shut with 200ish channels imported but takes 13 minutes to do so. Any thoughts what it could be doing?

[HappyTalk]

..

[rtv]

Originally Posted by ronilse

Hi,
Have you tried with adding new entries in MP after this fix (this gives errors & a crash in MP with latest SVN)?

Regards
Roy

This might be because idActor isn't unique. To fix your db try this

BEGIN TRANSACTION;
DROP INDEX actorinfo_idActor;
DROP INDEX actorinfomovies_idActor;
CREATE INDEX actorinfo_idActor ON actorinfo(idActor ASC);
CREATE INDEX actorinfomovies_idActor ON actorinfomovies(idActor ASC);
COMMIT;

I'll investigate it and maybe add it to the creation code of the db. So please do further tests ;-)

[Johnny-D]

HappyTalk: thanks for the offer, but I've got nowhere to upload my file to. Would it be possible for you to check out your own one and paste the SQL code you think should work? I'm pretty savvy and have a dummy install to play with so I'm sure could figure it out.

On the subject of the TV db, looks like it's already been highlighted a couple of months ago here http://forum.team-mediaportal.com/viewtopic.php?t=15633

Which is a relief because I thought my command-line SQL updates were causing the problems! Think the EPG wiki needs to warn that the import can take up to half an hour.

Something still seems off efficiency-wise but this stage of dev isn't the time to make a song and dance about it, and it might be that indexes could help there anyway.

[ronilse]

Hi,
Followed rtv's change & now it doesn't crash here. Will test this further this weekend, nice spotted HappyTalk

Regards
Roy