Thanks to all who have replied, very informative! :)
This is just a database for own personal use so it's not a big deal in any
way, mainly trying to get a better understanding of how Sqlite works here.
I'll note that the sql queries are not static inside my application but they
are generated
On Sunday, 17 March, 2019 11:19, niklas wrote:
>I agree that correlated subqueries in general seem more natural and
>are probably also less likely to have the performance pessimizations
>noticed with joins.
>But I might also want to use the column, or in case of a correlated
>subquery, the
On 17 Mar 2019, at 5:19pm, niklas wrote:
> Is this a recent change in Sqlite or have I misunderstood something? The
> Sqlite documentation still does not seem to say that column aliases can be
> used in the WHERE clause at least.
You are correct in two things. Column aliases cannot be relied
I agree that correlated subqueries in general seem more natural and are
probably also less likely to have the performance pessimizations noticed
with joins.
But I might also want to use the column, or in case of a correlated
subquery, the column alias, in the WHERE clause and previously that has
On 15 Mar 2019, at 7:02pm, niklas wrote:
> The data used for sqlite_stat1 in create.txt is taken from the real data,
> it's copied from the sql-dump generated just after running ANALYZE.
Okay. I should have guessed that. Sorry for doubting you.
You seem to have figured out a work-around for
The data used for sqlite_stat1 in create.txt is taken from the real data,
it's copied from the sql-dump generated just after running ANALYZE.
I only wanted to include the minimum amount of data the demonstrate the
issue so I omitted all other tables, views and data. As I understand it
sqlite only
nal Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf
>Sent: Friday, 15 March, 2019 14:44
>To: SQLite mailing list
>Subject: Re: [sqlite] Query planner: Scanning subqueries vs using
>automatic covering index
>
>
gt;From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of niklas
>Sent: Friday, 15 March, 2019 01:36
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Query planner: Scanning subqueries vs using
>automatic covering index
>
>I rece
On 15 Mar 2019, at 7:35am, niklas wrote:
> Dropping analyze (sqlite_stat1) information from the database made
> the problem go away, i.e. made sqlite use index instead of scanning.
Instead of dropping ANALYZE information, did you try updating it instead, by
running ANALYZE again ?
Ah ...
>
I recently noticed some very slow queries for my sqlite book database.
Turns out that it happened due to the query planner decided to scan
sub-queries instead of using an automatic covering index to search them.
The database contains about 3000 entries and with two subqueries it
took a
10 matches
Mail list logo
