Building Playlists

[larrettp]

The attached file contains the SqueezeCenter 7.0 table definitions, as you can see there are already a lot of indexes setup for both the main tables and the many-to-many tables. So I'm not sure if you just think that no indexes exist or if you have really verified that there are indexes missing that ought to be there.

I haven't verified this 100%, but I think all the columns which are included in a join directive should be indexed and probably also most (if not all) of the columns which are part of where directives.

If you enable the "database.sql" logging parameter in SqueezeCenter settings, you should be able to see exactly how the SQL statements that are executed looks like.

Thanks for this. I'll have a look at it in detail over the next couple of days. From the first look at it, I can see a lot of my suspicions confirmed but I want to check my facts and try to offer a contribution to a solution before I say any more.

[Philip Meyer]

You could try my plugin "PlaylistMan", which allows a song in the now playing playlist to be added to the end of another playlist. The way this is done is much quicker than loading the playlist, adding a song to it and then saving it again.

http://www.hergest.demon.co.uk/Music/music.shtml

[Phil Leigh]

Current playlists are stored in the database. So when you add a playlist of 7500 tracks then you're doing 7500 table INSERTs. The time taken is always going to be directly proportional to the size of the playlist.

Not to mention that if you're shuffling the playlist then it's all being done in memory on a server platform that can barely get out of its own way. There was a post a while back from someone at Infrant (I may actually have read it in the Infrant forums) explaining why. When SlimServer 6.0 came out, with its move to MySQL for its dbms, it was faster on many systems with large libraries, slower on some systems with small libraries, but very much slower on the ReadyNAS. It was explained by Infrant that the ReadyNAS CPU architecture was uniquely unsuited to running MySQL.

7,500 inserts on an indexed table on a PC might take a while...

On the other hand, all main queries need to be index-covered so you only read the index, not the data, wherever possible.

There is a read/write trade-off here, but since a database is (usually) write once, read many times, its best to invest the performance in the read rather than the write (at least in this application - unlike say a shopping cart, or airline system...)

I get annoyed by the length of time a scan takes (even with 7.x which is much faster than before)...but actually its the read performance that drives the user experience.

I don't get why it takes 60 mins to do 30k updates. I can't help wondering if the old trick of dropping all indexes, rescanning and then re-adding the indexes back at the end (obviously only for a clear & rescan!) would improve matters.

Doing updates to a table with a physically-clustered index is always slow...

Also, is it just me (I don't use playlists at all) but isn't 7.5k a bit BIG for a playlist?

[larrettp]

7,500 inserts on an indexed table on a PC might take a while...

On the other hand, all main queries need to be index-covered so you only read the index, not the data, wherever possible.

There is a read/write trade-off here, but since a database is (usually) write once, read many times, its best to invest the performance in the read rather than the write (at least in this application - unlike say a shopping cart, or airline system...)

I get annoyed by the length of time a scan takes (even with 7.x which is much faster than before)...but actually its the read performance that drives the user experience.

I don't get why it takes 60 mins to do 30k updates. I can't help wondering if the old trick of dropping all indexes, rescanning and then re-adding the indexes back at the end (obviously only for a clear & rescan!) would improve matters.

Doing updates to a table with a physically-clustered index is always slow...

Also, is it just me (I don't use playlists at all) but isn't 7.5k a bit BIG for a playlist?

I totally agree with all that you say about the Read/Write tradeoff. I've downloaded MySQL to my Mac and I'm currently experimenting with a few interesting combinations. I also found that the many-to-many records have Foreign Keys (with CASCADE ON DELETE) on them, which could be affecting things. I realise that this is a case of making the database fit the code - which is the wrong way round - but I don't want to instigate any code changes at the moment until I've proved the principle.

As for the size of my playlists, there shouldn't be a limit on such things (iTunes doesn't have one and it handles them instantly). I just have very eclectic taste and I enjoy the element of surprise!

More news later when I've finished the tests I'm currently running.

[Phil Leigh]

I totally agree with all that you say about the Read/Write tradeoff. I've downloaded MySQL to my Mac and I'm currently experimenting with a few interesting combinations. I also found that the many-to-many records have Foreign Keys (with CASCADE ON DELETE) on them, which could be affecting things. I realise that this is a case of making the database fit the code - which is the wrong way round - but I don't want to instigate any code changes at the moment until I've proved the principle.

As for the size of my playlists, there shouldn't be a limit on such things (iTunes doesn't have one and it handles them instantly). I just have very eclectic taste and I enjoy the element of surprise!

More news later when I've finished the tests I'm currently running.

Hi there - please ignore my comment about playlists as I don't use them...but I do design very large (>10Tb) commercial databases for a living!

(OK so they don't use MySQL...but the general principles tend to apply!)

That "cascade on delete" looks worrying ) - also, what are these m:m relationships? - they tend to not work at all! (I'm a third-normal form type of guy ) )

[larrettp]

Hi there - please ignore my comment about playlists as I don't use them...but I do design very large (>10Tb) commercial databases for a living!

(OK so they don't use MySQL...but the general principles tend to apply!)

That "cascade on delete" looks worrying ) - also, what are these m:m relationships? - they tend to not work at all! (I'm a third-normal form type of guy ) )

I'm a Mainframe DBA, so that's how my interest in this came about. I must admit, I'd never touched MySQL until today but, as you say, the same principles apply.

The thing about this whole design that gets me is the use of the many-to-many's - especially with the RI turned on so aggressively. I'll keep you posted about any results. Currently, in the absence of any programme information but based solely on the SQL logs, I've removed the RI and Indexed the affected tables up to the eyeballs! The initial results are encouraging, despite the slightly less han scientific methodology.

[Phil Leigh]

I'm a Mainframe DBA, so that's how my interest in this came about. I must admit, I'd never touched MySQL until today but, as you say, the same principles apply.

The thing about this whole design that gets me is the use of the many-to-many's - especially with the RI turned on so aggressively. I'll keep you posted about any results. Currently, in the absence of any programme information but based solely on the SQL logs, I've removed the RI and Indexed the affected tables up to the eyeballs! The initial results are encouraging, despite the slightly less han scientific methodology.

Hmmm m:m+RI... does not compute, Captain!

Surely those m:m's have got to go (I know that means redesigning the tables/indexes/keys but even so...)

Also - as I'm sure you know - RI only makes sense for 3rd+ normal form.

Anyway, I'm sure you know exactly what you are doing!

RI is really there (at least in this application) to cascade the deletes...but on a clear/re-scan you are in a database truncate situation...
RI won't help you there. Also (unless I'm missing something) RI is used to enforce parent-child (1:m) integrity for inserts...I'm thinking manual data entry here). In a "batch" situation, you can force all the parents in first (sorry - starting to ramble now - it's been a long day at the office)

[larrettp]

Hmmm m:m+RI... does not compute, Captain!

Surely those m:m's have got to go (I know that means redesigning the tables/indexes/keys but even so...)

Also - as I'm sure you know - RI only makes sense for 3rd+ normal form.

Anyway, I'm sure you know exactly what you are doing!

RI is really there (at least in this application) to cascade the deletes...but on a clear/re-scan you are in a database truncate situation...
RI won't help you there. Also (unless I'm missing something) RI is used to enforce parent-child (1:m) integrity for inserts...I'm thinking manual data entry here). In a "batch" situation, you can force all the parents in first (sorry - starting to ramble now - it's been a long day at the office)

Exactly! The m:m's plus the RI is not at all sensible. However, as I said, without access to the code, I'm in a position of trying to make the DB fit the code (tail wagging the dog?). The use of RI is ridiculous here because, as you said earlier, the only time the DB is written to is when scanning or compiling a playlist. Any checks on parentage would be done naturally in the code there. In fact, now I've removed it, it doesn't seem to have affected functionality at all (no surprise there).

I've been ranting on about the feeling I've had about the database for some time now and it's nice to have some confirmation that this is an avenue worth pursuing.

It would be much more worthwhile if the code could be changed to fit a more sensible design, though.

[egd]

However, as I said, without access to the code, I'm in a position of trying to make the DB fit the code (tail wagging the dog?)

The code is freely available for download if that will help you decipher.

[larrettp]

Can you let me know where it is, please?