Re: [sqlite] query optimization with "order by" in a view

2013-02-20 Thread Gabriel Corneanu
I admit I didn't check what the standards say about "select", I just wanted to make sure the potential users (which are by no means "developers") get the data properly. But you misread my example, I had "order by id" everywhere (no mixed sorting). I expected that the optimizer would "see" it's

Re: [sqlite] query optimization with "order by" in a view

2013-02-19 Thread Konrad Hambrick
> -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf > Of James K. Lowden > Sent: Tuesday, February 19, 2013 12:07 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] query optimization with

Re: [sqlite] query optimization with "order by" in a view

2013-02-19 Thread James K. Lowden
On Tue, 19 Feb 2013 10:19:26 +0100 "Gabriel Corneanu" wrote: > I included the "order by" in view because it's meant for some > end-users and I wanted to avoid mistakes. ... > Am I doing a mistake?? Well, yes, by including ORDER BY in the view definition. Most DBMSs

Re: [sqlite] query optimization with "order by" in a view

2013-02-19 Thread Richard Hipp
On Tue, Feb 19, 2013 at 8:30 AM, Gabriel Corneanu wrote: > I hoped it was either a slip or would be relatively simple to implement. > Good rule of thumb: Nothing is ever simple in a query optimizer -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] query optimization with "order by" in a view

2013-02-19 Thread Gabriel Corneanu
It's hard to accept this conclusion... it seems like a simple justification. If you say so, why is "select from v order by id" not doing a sort (with the data from view)? Obviously it "sees" the id is the primary key and uses it for sorting. I read here lots of messages about complex query

Re: [sqlite] query optimization with "order by" in a view

2013-02-19 Thread Simon Slavin
On 19 Feb 2013, at 9:19am, "Gabriel Corneanu" wrote: > As a summary, it seems that having multiple "order by" disturbs the query > builder; of course, I expected the "optimizer" to recognize that i was the > same order and avoid extra sorting. > Am I doing a

[sqlite] query optimization with "order by" in a view

2013-02-19 Thread Gabriel Corneanu
I need some clarifications on this issue. Here is a simplified example. There is a table: CREATE TABLE t(id integer primary key, data integer); and a (simplified) view: CREATE VIEW v as SELECT * FROM "t" order by id; I included the "order by" in view because it's meant for some end-users and I