Search This Blog

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)

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 ADD INDEX idx_idArtist(idArtist);
ALTER TABLE ADD INDEX idx_idGenre(idGenre);
ALTER TABLE ADD INDEX idx_idAlbum(idAlbum);

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


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

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

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

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

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

  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.

  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.

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

  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

  10. got anymore usefull settings for my.ini?

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