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
