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 wanted to avoid mistakes.
BUT I queried the view myself; I wanted to also be "safe" and included
another "order by":
explain query plan SELECT * FROM "v" order by id;
As one can see, the plan uses 2 scans and an extra sort (btree)!! Which
can be very expensive, of course...
I could find the following:
- if I query the view w/o "order by", it works as expected (uses primary
key)
- if I define the view w/o "order by", and use "order by" in query, it
also works as expected
- if I use the query with "order by rowid" (instead of id), the query plan
is a little different; it has 2 scans, but it doesn't use a temporary
btree anymore
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 mistake??
Thanks,
Gabriel
--
Using Opera's revolutionary email client: http://www.opera.com/mail/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
