@fizze wrote:
True, but a more sophisticated DB structure almost certainly speeds up the SQL-like backends.
I’d encourage you to try that supposition with a representative data set on a 16Mb embedded machine. I have, and got counter-intuitive results. That’s why the switch for in-order playlist results. As soon as you try and join tables, sqlite tries to do it either in memory or to a temp table, and it chokes.
Normally one would design the DB to reflect the song structure.
Thus having one table with the “Song” related info, and a connecting table to albums and artists.That way one could parse by albums, neglecting the artist join, and vice versa. Or just browse by songs.
Compilations would be a hybrid link table and join to songs, artists and albums.
And that’s how I would do it if it were an account-receivable program, and was going to run on a mssql cluster or something. 🙂
Sure memory load would eventually become an issue, but then again each table would hold less data, effectively reducing memory load.
Except with song-per-row, my memory overhead is one row, as I can do a rowscan and return the results without carrying a resultset or temp table in memory. No overhead. Scales to any n.
And a join with a single key over 2 tables really shouldn’t be that hard on the slug.
I agree with the GDBM thingy though.
Hm, would be nice to poke around with SQLite a bit on the slug and try to compare one approach vs the other…..
Look at the code that returns playlist results in-order. It’s a single query that does a join between playlists and songs. It’s probably representative of a join from songs to artists or vice versa. Time the queries, and decide where the time comes from.
But do it on a memory constrained device, and you’ll see the difference is best measured in orders of magnitude.
I’m certainly not a db expert, so if you find an elegant, fast solution, I’d be happy to take it!
— Ron