On 23 Jan 2015, at 4:59pm, Emmanouil Karvounis man...@di.uoa.gr 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
On 23 Jan 2015, at 4:15pm, Emmanouil Karvounis man...@di.uoa.gr wrote:
In short, we have two tables that are already sorted on a combination of
two fields
There is no such thing as a 'sorted table' in SQL. Each table is a set of rows
and the rows have no order.
If you want to make it easy
Thank you Clemens.
In the general case, myagg() doesn't have the appropriate property to
distribute over tableA and tableB seperately.
Let me be clear, we are not looking for alternatives to get our query
running more efficiently, we sincerely believe that we have found a defect
in the sqlite
Dear Simon,
Thank you for your answer and I'm sorry if I have used inappropriate
wording and confused you.
The issue is actually very simple:
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
To explain that with an example:
tableA
(1,2) (2,3) (2,4) (3,5)
tableB
(1,2) (2,3) (4,6)
Get a pointer on tableA and one on tableB.
(1,2) and (1,2) form a group, run count(*) and output 2.
Advance both pointers.
(2,3) and (2,3) form a group, run count(*) and output 2.
Advance both
Emmanouil Karvounis wrote:
In short, we have two tables that are already sorted on a combination of
two fields (which are their primary keys) and we want to union them and
apply group by on both the aforementioned fields, like so:
select c1, c2, myagg(*) from (
select * from tableA
union
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
On 1/23/15, Emmanouil Karvounis man...@di.uoa.gr wrote:
We have two tables that are already sorted on a combination of
two fields (which are their primary keys) and we want to union them and
apply group by on both the aforementioned fields, like so:
select c1, c2, myagg(*) from (
select *
Thank you very much, Richard.
I'm sure this enhancement, modelled in a more general way, e.g., like an
isSorted flag on the subquery to be used by the outer query, can be a great
enhancement for many other types of queries employing nesting. I think it
will help both in time and in space
Greetings,
We are having an issue with the sqlite query plan for one of our queries,
which seems to be sub-optimal both in time and in space complexity.
In short, we have two tables that are already sorted on a combination of
two fields (which are their primary keys) and we want to union them
10 matches
Mail list logo
