=?ISO-8859-1?Q?Tobias_Rundstr=F6m?= <[EMAIL PROTECTED]> wrote: > > 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);
source_idx and key_source_val_idx are redundant. Your queries will run just as fast if you DROP source_idx and keep just key_source_val_idx. And your INSERTs, UPDATEs, and DELETEs will be a little faster, since there is one fewer index to maintain. This is just an aside - it is not the cause of your problems. > > 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; I downloaded your database and the query above was indeed slow. But then I ran "ANALYZE" so that SQLite can gather statistics on the various indices, then reran the query. This time, SQLite was able to use the ANALYZE results to make better index choices and the query is quite speedy. The results of ANALYZE are stored in a special table named "sqlite_stat1". So you only have to run it once and the result will be used for all subsequent queries. -- D. Richard Hipp <[EMAIL PROTECTED]>
