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);