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.db3"
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.
------------
Add indexes to music database:-
1) Save the one below as index_music.sql and similarly import into musicdatabase5.db3
------------
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:-
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.
------------
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.db3"
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.
Code:
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
Code:
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:-
Code:
SELECT movieinfo.strTitle FROM movieinfo
JOIN genrelinkmovie ON movieinfo.idMovie = genrelinkmovie.idMovie
WHERE (genrelinkmovie.idGenre=104) ORDER BY movieinfo.strTitle LIMIT 32,16;