@fizze wrote:
wow, that DB code looks weird.
is idx an auto-increment field? it should be a unique key anyhow. in oracle you do that with a sequence. anywho….
No, it’s not. The key is “id”, and that is an auto increment. idx is part of a composite key, along with file path. So for example, an iso image might have a composite key of someiso.iso,1 someiso.iso,2 being first and second track in the iso image. That way you have have one physical file (or script) with multiple song entries. It should default to 0 though. :/ Guess I should look at my table definition again.
the statement
insert into playlistitems select NULL,154,id from songs order by time_added desc limit 100;[/code]
looks weirdish too.
sqlite doesn’t have a full parser, and stuff that would be illegal on other sql implementations is cheerfully accepted. 🙂 So it doesn’t *require* the parenthesis around the subselect, although sql99 clearly does. My bad.
INSERT INTO playlistitems
(SELECT NULL, 154, id
FROM songs
ORDER BY time_added DESC limit 100);that doesnt even look like proper SQL to me. you probably meant:
(SELECT id from SONGS ORDER by time_added DESC limit 100);in that sub-select.
that deffo does a full table scan, since there are no indices.
I guess the last 100 indices of songs would suffice, since its a auto-increment field, or sequence of some-sort, right?
Nope, that’s sql99. Just selecting a 3-tuple into playlistitems where two of the columns are constant and the other is the result of the query.
As far as a table scan, yeah. Sure does. But if it’s run as part of a nightly cron script or something, that wouldn’t be so bad.
Last 100 id’s isn’t quite right either — they would get reset after a full rescan. The date_added is based on ctime, so is probably a better indicator than id. At least after a full rescan.
— Ron