On 23 Jan 2015, at 4:59pm, Emmanouil Karvounis <[email protected]> wrote:
> tableA and tableB have both primary key on (c1, c2)
>
> explain query plan
> select c1, c2, count(*) from (
> select c1, c2 from tableA
> union all
> select c1, c2 from tableB
> )
> group by c1,c2
>
> 2|0|0|SCAN TABLE tableA USING COVERING INDEX sqlite_autoindex_tableA_1
> 3|0|0|SCAN TABLE tableB USING COVERING INDEX sqlite_autoindex_tableB_1
> 1|0|0|COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)
> 0|0|0|SCAN SUBQUERY 1
> 0|0|0|USE TEMP B-TREE FOR GROUP BY
>
> There is no reason to create a new temp B-tree when you can sequentially
> and in-synch scan the B-tree of tableA and of tableB and get the groups in
> one pass.
Sorry, but SQLite does not understand how the subquery (inside the brackets) is
going to be used by the main query. It hqs to complete the subquery first and
only then can it inspect the main query to find out how to optimize it. This
is not a bug, there just isn't enough flexibility to do this the way you want.
One of the following may or may not be useful:
You may be able to use
select c1, c2, myagg(*) from (
select c1, c2 from tableA group by c1,c2
union all
select c1, c2 from tableB group by c1,c2
) group by c1,c2
Alternatively, is there a good reason for tableA and tableB not to be merged
with, perhaps, an extra column indicating 'A' or 'B' ? This would allow you to
create an index and get your answer almost instantly. When you see two tables
with the same columns it's often an indication that there should really be one
table.
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
