Hi List,
The below query takes just 0.002 seconds to run (sqlite 3.15.0 and
3.23.0 (preview)) despite looking through hundreds of thousands of
records in each table, and it returns 86 records in all. This is great!
But when I stick an "ORDER BY" on the end (either ASC or DESC), the
processin
On 22 Mar 2018, at 9:24pm, Jonathan Moules wrote:
> But when I stick an "ORDER BY" on the end (either ASC or DESC), the
> processing time shoots up to 0.15s. The EXPLAIN between the two is
> considerably different so it seems the ORDER BY is getting it to use a
> sub-optimal query plan.
Hi, J
Quick workaround: put a "+" on front of the first term of your ORDER BY
clause.
On Thursday, March 22, 2018, Jonathan Moules
wrote:
> Hi List,
>
> The below query takes just 0.002 seconds to run (sqlite 3.15.0 and 3.23.0
(preview)) despite looking through hundreds of thousands of records in each
Hi Simon,
Sure; I didn't include them because the only difference is the last
line, and that just seems to be the standard "ordering" line. I figured
the explain was more useful as a lot has changed in that.
Cheers,
Jonathan
Fast version:
100SEARCH TABLE lookups USING COVERING IND
On 22 Mar 2018, at 10:09pm, Jonathan Moules
wrote:
> Sure; I didn't include them because the only difference is the last line, and
> that just seems to be the standard "ordering" line. I figured the explain was
> more useful as a lot has changed in that.
I find EXPLAIN QUERY PLANs easier to r
When I saw this post I just assumed there wasn't a sufficient index to
handle the select and the order by.
Curious about the suggestion of adding + to the order by first term.
On Thu, Mar 22, 2018 at 3:14 PM Simon Slavin wrote:
> On 22 Mar 2018, at 10:09pm, Jonathan Moules
> wrote:
>
> > Sure
On 22 Mar 2018, at 10:16pm, Mark Wagner wrote:
> Curious about the suggestion of adding + to the order by first term.
This stops SQLite from realising it can use an existing index. If you do
CREATE INDEX m_s ON members (score)
SELECT * FROM members ORDER BY score DESC
SQLite cleverly
On 2018-03-22 22:08, Richard Hipp wrote:
Quick workaround: put a "+" on front of the first term of your ORDER BY
clause.
This gives me an ending of:
ORDER BY
+u.url_id ASC
LIMIT 1;
Alas it makes no difference to the speed. The sole difference in the
EXPLAIN plan when that's added from the
Hi Simon,
Yep, I too find the Query Plan's easier to read (sometimes I even think
I understand bits of them!)
I do expect SQLite to get slower with an Order By - it has more work to
do after all, but I thought I'd ask for this one because it's slowing
down by almost two orders of magnitude (a
On 23 March 2018 at 05:24, Jonathan Moules
wrote:
> Hi List,
>
> The below query takes just 0.002 seconds to run (sqlite 3.15.0 and 3.23.0
> (preview)) despite looking through hundreds of thousands of records in each
> table, and it returns 86 records in all. This is great!
>
> But when I stick a
10 matches
Mail list logo
