Thursday, March 31, 2011

Summary of XBMC MySQL database optimisations

These will make your MySQL installations perform noticeably quicker (especially with large music libraries!)

(Assumes a Windows MySQL installation, with user xbmc, databases call xbmc_music and xbmc_video)


In advancedsettings.xml:
Use the IP address and NOT the hostname of your MySQL server


In my.ini:
(add to mysqld section)
skip-name-resolve

At dos cmd:
mysqlcheck -op -u xbmc xbmc_video
mysqlcheck -op -u xbmc xbmc_music

In MySQL command tool:
use xbmc_music;
CREATE INDEX idAlbum_idx ON song(idAlbum);
CREATE INDEX idArtist_idx ON song(idArtist);
CREATE INDEX idArtist_idx ON album(idArtist);
CREATE INDEX idArtist_idx ON exartistsong(idArtist);
CREATE INDEX idArtist_idx ON exartistalbum(idArtist);
ALTER TABLE xbmc_music.song ADD INDEX idx_idArtist(idArtist);
ALTER TABLE xbmc_music.song ADD INDEX idx_idGenre(idGenre);
ALTER TABLE xbmc_music.song ADD INDEX idx_idAlbum(idAlbum);

use xbmc_video;
ALTER TABLE movie ADD INDEX idMovie(idMovie);
ALTER TABLE movie ADD INDEX idFile(idFile);

11 comments:

  1. Can you run these commands on a populated mySQL database without borking the existing data?

    ReplyDelete
  2. Can you run these on an existing database without borking the data?

    ReplyDelete
  3. Yep, no problem - it just creates indexes which speeds things up but doesn't change anything else.

    ReplyDelete
  4. please i need your help how can i add synopsis or metadata to the movies on xbmc. please

    ReplyDelete
  5. You might want to look at tools like Ember etc. See the XBMC forums.

    ReplyDelete
  6. Use "mysqlshow -u xbmc -p" to find your music and movie database names.

    Assuming user for database is xbmc, per how-to instaructions from XBMC website.

    ReplyDelete
  7. I have just updated to Eden - RC2. Having a look at the database structure it has created I think these optimizations have already been added, do you know if that is correct?

    With my *very* limited mySql knowledge it would appear these indexes are already created.

    ReplyDelete
  8. Yeah all the index stuff is integrated into Eden now.
    Thw skip-name-resolve is about all you need to do these days.

    ReplyDelete
  9. with the skip name resolve where exactly does it need to go in the my.ini file? Also if I try adding it to where I think it needs to go I cannot save the file. Thanks for your help

    ReplyDelete
  10. got anymore usefull settings for my.ini?

    ReplyDelete
  11. Not really, don't find I need any, seems to work well with the defaults

    ReplyDelete

Note: Only a member of this blog may post a comment.