On 09/20/2014 10:59 AM, Yuanzhong Xu wrote:
I think this is related to a check for restriction (18) in subquery flattening.
(18) If the sub-query is a compound select, then all terms of the
ORDER by clause of the parent must be simple references to
columns of the sub-query.
Quite correct. Internally, SQLite 'flattens' the first query to:
SELECT id, data FROM t1 WHERE id=10
UNION ALL
SELECT id, data FROM t2 WHERE id=10
ORDER BY data
Which can be executed efficiently. But it does not currently do the same
trick for the second, as:
SELECT id FROM t1 WHERE id=10
UNION ALL
SELECT id FROM t2 WHERE id=10
ORDER BY data
is not a valid statement in SQLite.
Dan.
Thanks,
Yuanzhong
On Fri, Sep 19, 2014 at 8:16 PM, Yuanzhong Xu <y...@utexas.edu> wrote:
Hi,
In SQLite 3.8.6, suppose I have two tables in the same format:
CREATE TABLE t1 (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
CREATE TABLE t2 (id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
The query plan can be quite efficient for:
EXPLAIN QUERY PLAN SELECT id,data FROM (SELECT * FROM t1 UNION ALL
SELECT * FROM t2) WHERE id=10 ORDER BY data;
1|0|0|SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
2|0|0|SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)
However, if you remove "data" column from the outer SELECT, it becomes
very inefficient:
EXPLAIN QUERY PLAN SELECT id FROM (SELECT * FROM t1 UNION ALL SELECT *
FROM t2) WHERE id=10 ORDER BY data;
2|0|0|SCAN TABLE t1
3|0|0|SCAN TABLE t2
1|0|0|COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
0|0|0|SCAN SUBQUERY 1
0|0|0|USE TEMP B-TREE FOR ORDER BY
Thanks,
--
Yuanzhong Xu
Ph.D. Student
Department of Computer Science
the University of Texas at Austin
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
