Hi. You're joining the table with itself using 'id'. However, there's no index just on that field. I'm not sure how exactly SQLite utilises indices, but it might be unable to use index on (id,key,source) triple to optimise calculation of "m1.id = m2.id".
Does this sound sensible? Cheers. --- =?ISO-8859-1?Q?Tobias_Rundstr=F6m?= <[EMAIL PROTECTED]> wrote: > Hello, > > I guess this subject is a bit worn out. But I am having scalabillity > problems with SQLite in XMMS2. We have dimensioned XMMS2 to handle > insanely huge media libraries, playlists and clients. Our goal is to > be able to run medialibs with 50.000 files without problem. Our > backend is SQLite3. When we get somewhere around 160000 rows (10k > entries) we're starting to see problems with scalabillity of SQLite3. > I would like some views on how we could speed up the storage backend > and also comments on our structure. > > Today we save all data in one table called Media. Each entry has a id > number and each id number can have a indefinitive number of > properties. To keep this flexible and clean we choose to add one row > per property. A property can be "artist", "album", "url" and more. > The schema is this: > > create table Media (id integer, key, value, source integer); > and the indexes: > create unique index key_idx on Media (id,key,source); > create index prop_idx on Media (key,value); > create index source_idx on Media (key,source); > create index key_source_val_idx on Media (key,source,value); > > The most common query is something like this: > > select value from Media where id=5 and key='url' and source=1; > > This query remains very fast no matter how many entries I have in my > database also things like: > > select key, value from Media where id=5 and source=1; > > is still very fast. > > But more advanced queries like "show me all albums and artists that > are not compilations" are very slow: > > select distinct m1.value as artist, ifnull(m2.value,'[unknown]') as > album from Media m1 left join Media m2 on m1.id = m2.id and > m2.key='album' left join Media m3 on m1.id = m3.id and > m3.key='compilation' where m1.key='artist' and m3.value is null; > > In fact, whenever I join with myself and try to extract a big number > of values it can take forever to get the result. > > I have tried to increase the cache_size to somewhere around 32000 to > see if it made any difference, it didn't. > idxchk tells me that the good indexes are in use. > > Any comments, help or blame is welcome to try to solve this issue of > scalabillity. You can download a medialib here: > http://debian.as/~skid/medialib.db.gz this contains almost 200000 > rows and 14000 songs and is a real user library. > > Looking forward to getting your input. > > Thanks > Tobias > > > ---------- > * Zoner PhotoStudio 8 - Your Photos perfect, shared, organised! > www.zoner.com/zps > > __________________________ Stops spam 100% for your email accounts or you get paid. http://www.cashette.com
