HowTo Improve Movie & Music database speed 1,000 times

Discussion in 'Tips and Tricks' started by HappyTalk, August 11, 2006.

  1. HappyTalk

    HappyTalk Portal Pro

    Joined:
    July 16, 2006
    Messages:
    307
    Likes Received:
    8
    Occupation:
    Coder
    Location:
    UK
    Ratings:
    +8 / 0
    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.



    Code (Text):
    1. BEGIN TRANSACTION;
    2. CREATE INDEX actorlinkmovie_idActor ON actorlinkmovie(idActor ASC);
    3. CREATE INDEX actorlinkmovie_idMovie ON actorlinkmovie(idMovie ASC);
    4. CREATE INDEX actors_strActor ON actors(strActor ASC);
    5. CREATE INDEX files_idMovie ON files(idMovie ASC);
    6. CREATE INDEX files_idPath ON files(idPath ASC);
    7. CREATE INDEX genrelinkmovie_idGenre ON genrelinkmovie(idGenre ASC);
    8. CREATE INDEX genrelinkmovie_idMovie ON genrelinkmovie(idMovie ASC);
    9. CREATE INDEX movie_idPath ON movie(idPath ASC);
    10. CREATE INDEX movieinfo_iYear ON movieinfo(iYear ASC);
    11. CREATE INDEX movieinfo_idDirector ON movieinfo(idDirector ASC);
    12. CREATE UNIQUE INDEX movieinfo_idMovie ON movieinfo(idMovie ASC);
    13. CREATE INDEX movieinfo_strTitle ON movieinfo(strTitle ASC);
    14. CREATE INDEX path_strPath ON path(strPath ASC);
    15. COMMIT;
    16.  
    ------------
    Add indexes to music database:-
    1) Save the one below as index_music.sql and similarly import into musicdatabase5.db3
    Code (Text):
    1. BEGIN TRANSACTION;
    2. CREATE UNIQUE INDEX idx_artist_strArtist ON artist(strArtist ASC);
    3. CREATE INDEX idx_album_idArtist ON album(idArtist ASC);
    4. CREATE INDEX idx_album_strAlbum ON album(strAlbum ASC);
    5. CREATE UNIQUE INDEX idx_path_strPath ON path(strPath ASC);
    6. CREATE INDEX idx_song_idArtist ON song(idArtist ASC);
    7. CREATE INDEX idx_song_idAlbum ON song(idAlbum ASC);
    8. CREATE INDEX idx_song_idGenre ON song(idGenre ASC);
    9. CREATE INDEX idx_song_idPath ON song(idPath ASC);
    10. CREATE INDEX idx_song_strTitle ON song(strTitle ASC);
    11. CREATE INDEX idx_song_iYear ON song(iYear ASC);
    12. CREATE UNIQUE INDEX idx_genre_strGenre ON genre(strGenre ASC);
    13. 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 (Text):
    1. SELECT movieinfo.strTitle FROM movieinfo
    2.        JOIN genrelinkmovie ON movieinfo.idMovie = genrelinkmovie.idMovie
    3.        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.
     
  2. Google AdSense Guest Advertisement



    to hide all adverts.
  3. ronilse
    • Premium Supporter

    ronilse Retired Team Member

    Joined:
    July 19, 2005
    Messages:
    4,422
    Likes Received:
    280
    Gender:
    Male
    Occupation:
    MediaPortal tester
    Location:
    Moss
    Ratings:
    +280 / 0
    Home Country:
    Norway Norway
    Show System Specs
    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
     
  4. HappyTalk

    HappyTalk Portal Pro

    Joined:
    July 16, 2006
    Messages:
    307
    Likes Received:
    8
    Occupation:
    Coder
    Location:
    UK
    Ratings:
    +8 / 0
    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.
     
  5. Maschine
    • Premium Supporter

    Maschine Retired Team Member

    Joined:
    June 15, 2004
    Messages:
    768
    Likes Received:
    78
    Location:
    Germany
    Ratings:
    +84 / 0
    Home Country:
    Germany Germany
    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
     
  6. HappyTalk

    HappyTalk Portal Pro

    Joined:
    July 16, 2006
    Messages:
    307
    Likes Received:
    8
    Occupation:
    Coder
    Location:
    UK
    Ratings:
    +8 / 0
  7. Johnny-D

    Johnny-D Portal Member

    Joined:
    August 11, 2006
    Messages:
    31
    Likes Received:
    0
    Ratings:
    +0 / 0
    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?
     
  8. HappyTalk

    HappyTalk Portal Pro

    Joined:
    July 16, 2006
    Messages:
    307
    Likes Received:
    8
    Occupation:
    Coder
    Location:
    UK
    Ratings:
    +8 / 0
  9. rtv
    • Premium Supporter

    rtv Retired Team Member

    Joined:
    April 7, 2005
    Messages:
    3,622
    Likes Received:
    301
    Occupation:
    CTO at ES2000
    Location:
    Osnabruck
    Ratings:
    +301 / 0
    Home Country:
    Germany Germany
    Show System Specs
    This might be because idActor isn't unique. To fix your db try this
    Code (Text):
    1.  
    2. BEGIN TRANSACTION;
    3. DROP INDEX actorinfo_idActor;
    4. DROP INDEX actorinfomovies_idActor;
    5. CREATE INDEX actorinfo_idActor ON actorinfo(idActor ASC);
    6. CREATE INDEX actorinfomovies_idActor ON actorinfomovies(idActor ASC);
    7. COMMIT;
    8.  
    I'll investigate it and maybe add it to the creation code of the db. So please do further tests ;-)
     
  10. Johnny-D

    Johnny-D Portal Member

    Joined:
    August 11, 2006
    Messages:
    31
    Likes Received:
    0
    Ratings:
    +0 / 0
    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 https://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.
     
  11. ronilse
    • Premium Supporter

    ronilse Retired Team Member

    Joined:
    July 19, 2005
    Messages:
    4,422
    Likes Received:
    280
    Gender:
    Male
    Occupation:
    MediaPortal tester
    Location:
    Moss
    Ratings:
    +280 / 0
    Home Country:
    Norway Norway
    Show System Specs
    Hi,
    Followed rtv's change & now it doesn't crash here. Will test this further this weekend, nice spotted HappyTalk :)

    Regards
    Roy
     
Loading...

Users Viewing Thread (Users: 0, Guests: 0)

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice
  • About The Project

    The vision of the MediaPortal project is to create a free open source media centre application, which supports all advanced media centre functions, and is accessible to all Windows users.

    In reaching this goal we are working every day to make sure our software is one of the best.

             

  • Support MediaPortal!

    The team works very hard to make sure the community is running the best HTPC-software. We give away MediaPortal for free but hosting and software is not for us.

    Care to support our work with a few bucks? We'd really appreciate it!