On 1 Oct 2019, at 8:15pm, Fredrik Larsen wrote:
> I have run analyze on production data, that should work better, right?
Yes, that is the best way to do it. I've been given some databases where the
data saved by ANALYZE shows it was run with empty tables. So I sometimes warn
people that
I have run analyze on production data, that should work better, right? Or
do you propose to "trick" the query-planner using some kind of staging-data?
Anyway, I use INDEXED BY hints now, and this solves my problem.
Fredrik
On Tue, Oct 1, 2019 at 8:57 PM Simon Slavin wrote:
> When trying to
Yes, your note about DESC ordering is the only thing missing now, and then
everything will be perfect :) I have a build of sqlite with the
GROUP-BY-DESC patch applied, but not able to test now. But previous testing
using very similar queries did work fine so don't expect any problems.
Now all my
When trying to make SQLite pick the right index, please use this sequence.
1) Create your indexes.
2) Put typical example data in your table.
3) Use the ANALYZE command
4) Delete the test data and put in real data (optional)
You can do (1) and (2) in any order. But having data in the table
On Tuesday, 1 October, 2019 11:58, Fredrik Larsen wrote:
>Thanks Keith! I have spent several days trying to tune my query towards
>expected performance, without luck. I somehow missed your fairly straight
>forward solution. I still have some problems making sqlite use the
>correct indexes, but
It may not be perfect, but it is impressive. Impressive and frustrating are
not mutually exclusive :)
Anyway, I'm probably not a typical sqlite user. I use sqlite as a simple
indexing system, where all reads are expected to hit an index and return
instantly. For this project, direct control over
> On Oct 1, 2019, at 1:05 PM, Richard Hipp wrote:
> Alas, SQLite's query planner is not perfect.
...files bug report... “lacking perfection.”
;-)
-j
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
Richard Hipp, on Tuesday, October 1, 2019 02:05 PM, wrote...
>
> On 10/1/19, Fredrik Larsen, on
> >
> > The declarative model of SQL is nice, but when you care about performance,
> > it quickly gets frustrating and time consuming.
>
> In a perfect world, the query planner would recognize your
On 10/1/19, Fredrik Larsen wrote:
>
> The declarative model of SQL is nice, but when you care about performance,
> it quickly gets frustrating and time consuming.
In a perfect world, the query planner would recognize your intent and
do the right thing. You would be able to write the query any
Thanks Keith! I have spent several days trying to tune my query towards
expected performance, without luck. I somehow missed your fairly straight
forward solution. I still have some problems making sqlite use the correct
indexes, but this can at least be fixed by well-placed INDEXED-BY-hints.
The
ays a
lot about anticipated traffic volume.
>-Original Message-
>From: sqlite-users On
>Behalf Of Keith Medcalf
>Sent: Monday, 30 September, 2019 19:31
>To: SQLite mailing list
>Subject: Re: [sqlite] Lookup join
>
>
>You mean something like this:
&
: SQLite mailing list
>Subject: [sqlite] Lookup join
>
>Consider query below;
>
>SELECT key
>FROM t1
>LEFT JOIN (
> SELECT key,max(rev),data
> FROM t2
> WHERE rev < ?
> GROUP BY key
>) USING (key)
>ORDER BY key ?
>LIMIT ?
>
>In above query sqlit
Consider query below;
SELECT key
FROM t1
LEFT JOIN (
SELECT key,max(rev),data
FROM t2
WHERE rev < ?
GROUP BY key
) USING (key)
ORDER BY key ?
LIMIT ?
In above query sqlite will materialize the t2-sub-query and then start
working on the outer query. I have a lot of data in t2 so this will
13 matches
Mail list logo
