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
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.
---
Elcin Recebli [EMAIL PROTECTED] wrote:
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 =
El 24-03-2006, a las 16:08, [EMAIL PROTECTED] escribió:
Elcin Recebli [EMAIL PROTECTED] wrote:
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
=?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
Friday, March 24, 2006, 2:33:36 PM, Tobias Rundström wrote:
[...]
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
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
=?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
=?ISO-8859-1?Q?Tobias_Rundstr=F6m?= [EMAIL PROTECTED] wrote:
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
