Re: [sqlite] Why are two select statements 2000 times faster than one?

To answer my own question, whether there is an efficient way to find max() of an increasingly sorted column in a virtual array: What is needed is to make sure that xBestIndex sets orderByConsumed, and that the module takes care of all sorting. -- Steinar

Re: [sqlite] Why are two select statements 2000 times faster than one?

[Gerry Snyder] > At worst you could use another table to keep track of the maximum and > minimum, and update it with triggers when something is added to or deleted > from the virtual table. My module knows what the maximum and minimum values are at all times. It also knows that the column is

Re: [sqlite] Why are two select statements 2000 times faster than one?

[Kit] > 2012/4/15 Steinar Midtskogen : >> So, is there really a way to create an index in a virtual table, or a >> way to emulate this? > > Why? You don't need this. Use index on base tables. My base tables are indexed. Let's say I want to make a very simple virtual table

Re: [sqlite] Why are two select statements 2000 times faster than one?

At worst you could use another table to keep track of the maximum and minimum, and update it with triggers when something is added to or deleted from the virtual table. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Why are two select statements 2000 times faster than one?

2012/4/15 Steinar Midtskogen : > So, is there really a way to create an index in a virtual table, or a > way to emulate this? Why? You don't need this. Use index on base tables. > My xRowid function simply returns the value of the "unix_time" column, > but even "select

Re: [sqlite] Why are two select statements 2000 times faster than one?

[Simon Slavin] > On 15 Apr 2012, at 1:31pm, Steinar Midtskogen wrote: > >> Another question about max()/min() optimisation. Is there a way I can >> implement a virtual table so that max()/min() of a sorted >> (incrementing) column (which could be an integer primary key in

Re: [sqlite] Why are two select statements 2000 times faster than one?

Hello again, Another question about max()/min() optimisation. Is there a way I can implement a virtual table so that max()/min() of a sorted (incrementing) column (which could be an integer primary key in a regular table) gets fast? For example, sqlite> explain query plan select max(unix_time)

Re: [sqlite] Why are two select statements 2000 times faster than one?

On 04/14/2012 03:14 AM, Steinar Midtskogen wrote: Puneet Kishor writes: If you want the results in separate columns, you can do something like SELECT Min(a) minimum, 'none' maximum FROM t UNION ALL SELECT 'none' minimum, Max(a) minimum FROM t; Then it does a full scan

Re: [sqlite] Why are two select statements 2000 times faster than one?

On Apr 13, 2012, at 3:14 PM, Steinar Midtskogen wrote: > Puneet Kishor writes: > >> If you want the results in separate columns, you can do something like >> >> SELECT Min(a) minimum, 'none' maximum FROM t UNION ALL SELECT 'none' >> minimum, Max(a) minimum FROM t; > >

Re: [sqlite] Why are two select statements 2000 times faster than one?

Puneet Kishor writes: > If you want the results in separate columns, you can do something like > > SELECT Min(a) minimum, 'none' maximum FROM t UNION ALL SELECT 'none' minimum, > Max(a) minimum FROM t; Then it does a full scan again. But Igor's suggestion "SELECT (SELECT

Re: [sqlite] Why are two select statements 2000 times faster than one?

Try the following sqlite> EXPLAIN QUERY PLAN SELECT Min(a) FROM t UNION ALL SELECT Max(a) FROM t; selectid|order|from|detail 1|0|0|SEARCH TABLE t USING INTEGER PRIMARY KEY (~1 rows) 2|0|0|SEARCH TABLE t USING INTEGER PRIMARY KEY (~1 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL) Should be a

Re: [sqlite] Why are two select statements 2000 times faster than one?

On 4/13/2012 2:59 PM, Steinar Midtskogen wrote: I have a table with "unix_time" as primary key and I want to get the minimum and maximum values of "unix_time". When I do: SELECT min(unix_time), max(unix_time) from table; it is very slow. It takes about 250ms, nearly everything in the

Re: [sqlite] Why are two select statements 2000 times faster than one?

Alessandro Marzocchi writes: > What does EXPLAIN QUERY PLAN says? sqlite> EXPLAIN QUERY PLAN SELECT min(unix_time) FROM table; 0|0|0|SEARCH TABLE table USING INTEGER PRIMARY KEY (~1 rows) sqlite> EXPLAIN QUERY PLAN SELECT max(unix_time) FROM table; 0|0|0|SEARCH

Re: [sqlite] Why are two select statements 2000 times faster than one?

What does EXPLAIN QUERY PLAN says? Il giorno 13 aprile 2012 21:04, Marc L. Allen ha scritto: > Maybe the query analyzer isn't smart enough to do two seeks in this case, > so it does a scan? > > > -Original Message- > > From: sqlite-users-boun...@sqlite.org

Re: [sqlite] Why are two select statements 2000 times faster than one?

Maybe the query analyzer isn't smart enough to do two seeks in this case, so it does a scan? > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Steinar Midtskogen > Sent: Friday, April 13, 2012 3:00 PM > To: