On 03-06-2011 14:29, Stéphane MANKOWSKI wrote:
> Hi,
>
> First, think you for doing sqlite. I like it !
>
> I am surprised by the result of "EXPLAIN QUERY PLAN" on some queries.
>
> I will explain try to explain my issue.
>
> 1-Open the attached database
> This database contains a table "a":
> CREATE TABLE a(id INT,v REAL,r_a_id INT)
> This table "a" is pointing to an other table named "c" through the
> attribute "r_a_id":
> CREATE TABLE c(id INT,name)
>
> The view "v_c" is representing that table "c" with one more column
> "total" computed by a SELECT on "a":
> CREATE VIEW v_c AS SELECT *, (SELECT TOTAL(a.v) FROM a WHERE
> a.r_a_id=c.id ) AS total FROM c
>
>
> 2-EXPLAIN QUERY PLAN SELECT id FROM v_c
> returns:
> SCAN TABLE c (~74 rows)
>
> This is correct
>
> 3-EXPLAIN QUERY PLAN SELECT id FROM v_c
> returns:
> SCAN TABLE c (~74 rows)
> EXECUTE CORRELATED SCALAR SUBQUERY 1
> SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows)
>
> This is correct too because we have to compute the "total" column
>
> 4-EXPLAIN QUERY PLAN SELECT total, total+1, total+2 FROM v_c
> returns:
> SCAN TABLE c (~74 rows)
> EXECUTE CORRELATED SCALAR SUBQUERY 1
> SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows)
> EXECUTE CORRELATED SCALAR SUBQUERY 2
> SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows)
> EXECUTE CORRELATED SCALAR SUBQUERY 3
> SEARCH TABLE a USING INDEX a_r_a_id (r_a_id=?) (~88 rows)
>
> For me this is not optimized (but I am not a db specialist) because the
> computation of "total" is done 3 times instead of only one.
>
> What do you think about that ?
> Is it a bug in the plan of execution or is it normal ?
> Do you know I can optimize my view to have only one "CORRELATED SCALAR" ?
>
> I tried "EXPLAIN QUERY PLAN SELECT total, total+1, total+2 FROM (select total
> from v_c)" but I have the same result.
>
> In advance, thank you for your help and your very good tool !
>
> Regards,
> Stéphane MANKOWSKI
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
create view v_c2 as
select c.id, c.name, total(a.v) total
from c
inner join a ON a.r_a_id=c.id
group by c.id, c.name;
sqlite> EXPLAIN QUERY PLAN SELECT total, total+1, total+2 FROM v_c2;
0|0|TABLE c
1|1|TABLE a WITH AUTOMATIC INDEX
sqlite>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
