[mythtv-users] MythMusic Limits??

[Chris Petersen]

I finally got around to indexing my music collection.  about 80 gigs of
mp3 and ogg files (a little over 16k tracks).  Took about 2 hours over
100bT, but it eventually finished.  It also takes a LONG time (3-4
minutes) to bring up anything in mythmusic (the sql queries just seem to
take a long time), though it'll eventually play its playlist.

Might I make a couple of suggestions to the developer(s) here?  (sorry
for the long DB rant - I'm a database guy, so I get nitpicky about this
sort of thing - it should be on a dev list, but I'm not on a dev list)

Go for the itunes/rhythmbox look - having to go back to the main music
list every time I want to change what I'm listening to is rather
annoying and time-consuming, especially with a lot of music.  Thus,
allow for browsing by artist or by album, but also allow for saved
playlists (would need to work on some way to name them without a
keyboard, but that's easy enough to do, no?)

Index things, but don't access the index unless you have to.  Also,
index things more intelligently:

the meta-data is currently stored in only one table:

intid	artist		album		title		genre
year	tracknum	length		filename
rating	lastplay	playcount 

You index the artist, album, title and genre separately, meaning that
when you search for something in the database, if it has one of these
fields, mysql must decide which index it wants to use (because they're
separate indexes, it can't use them at the same time).

the best way to do this, would be to create separate tables for artist,
album and genre.  That way, your index of these fields would be a
numeric ID, not a string (strings are slow - VERY slow).  It would also
improve load time, since mysql wouldn't have to search through every
single track record to get at some basic info...  Anyway, here's an idea
of what this would look like:

CREATE TABLE music_artists (
    artist_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    artist_name  VARCHAR NOT NULL DEFAULT ""    
);

CREATE TABLE music_albums (
    album_id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    album_name          VARCHAR NOT NULL DEFAULT "",
    primary_artist_id   INT UNSIGNED NOT NULL DEFAULT 0,
    other_artists       VARCHAR(240) NOT NULL DEFAULT "",
    
    INDEX(primary_artist_id, other_artists)
);

CREATE TABLE music_genres (
    genre_id    INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    genre       VARCHAR NOT NULL DEFAULT ""    
);

CREATE TABLE music_tracks (
    track_id    BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    artist_id   INT UNSIGNED NOT NULL DEFAULT 0,
    album_id    INT UNSIGNED NOT NULL DEFAULT 0,
    genre_id    INT UNSIGNED NOT NULL DEFAULT 0,
    
    title       etc...
    year
    tracknum
    length
    filename
    rating
    lastplay
    playcount
    
    INDEX (artist_id),
    INDEX (album_id),
    INDEX (genre_id)
)

Basically, mythmusic should follow one of the basic rules of relational
db design - don't duplicate data that could live in a related table. 
names are long, so you should store them once, and give them an id. 
Numbers are MUCH easier for a db to search through.

Anyway, I'd be happy to lend a hand with the db side of things on any of
the myth projects...

-Chris