...@gmail.com
Subject: Re: [sqlite] Aggregate and join query very slow
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Message-ID:
aanlktinqmk9csyaguoyllo2okpmsyq3di-tzbuahw...@mail.gmail.com
Content-Type: text/plain; charset=ISO-8859-1
On Thu, May 27, 2010 at 3:07 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Aggregate and join query very slow
Thank you very much. Both replies pointed to indexes. So I changed
the indexes and markedly improved performance from 12 seconds to about
1.5 seconds for the faster variant (using nested SELECTS) and about
2.2
(Max Vlasov)
Message: 3
Date: Thu, 27 May 2010 17:26:10 +0400
From: Max Vlasov max.vla...@gmail.com
Subject: Re: [sqlite] Aggregate and join query very slow
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Message-ID:
aanlktinqmk9csyaguoyllo2okpmsyq3di-tzbuahw
On 28 May 2010, at 3:57pm, Michael Ash wrote:
Per suggestions, I indexed year and media on the big table. So I now
have separate indexes for the key variable (releasenumber) and for
year and for media.Would it make more sense to have a single
index for all three, thus:
CREATE UNIQUE
I am new to sqlite3, converting from mysql.
A query that involves an aggregate function and a join is running
very slowly (taking about 15 seconds compared to mysql where it runs
in 1 second). I've tried two variants of the query (each reprinted
below with the explain query plan), and both are
. Black
Senior Scientist
Northrop Grumman Mission Systems
From: sqlite-users-boun...@sqlite.org on behalf of Michael Ash
Sent: Thu 5/27/2010 6:07 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Aggregate and join query very slow
I am new to sqlite3, converting
On Thu, May 27, 2010 at 3:07 PM, Michael Ash m...@econs.umass.edu wrote:
...These are large tables (52,355 records in facility and 4,085,137 in
release_cl).
...
sqlite explain query plan
... SELECT name,score
... FROM facility f, (SELECT facilitynumber,SUM(score_rev) AS score
...
