> > (select name, max(validfrom) as mvf from aliases where validFrom <= (select
> > strftime('%s', 'now')) group by name)
> I don't think that solves the problem because each time the group by query is
> executed, your subquery will be executed again.
Not possible.
WHERE conditions are evaluated once per row to select rows which are included
in GROUP BY operations.
HAVING conditions are evaluated per group to determine whether the group is a
part of the returned result set.
** of course, not all WHERE and HAVING are necessarily executed for every row
or group. If one of the conditions eliminates the row/group, then no further
constraints need evaluating, obviously **
The only case in which a WHERE condition is evaluated once per GROUP is when
all groups only contain a single row.
In any case, the generated execution plan clearly indicates that the scalar
subquery is only evaluated once.
Sqlite> .explain
sqlite> explain query plan select a.name, a.assignedTo, a.validFrom from
aliases as a join (select name, max(validfrom) as mvf from
aliases where validFrom <= strftime('%s', 'now') group by name) as b on
a.name=b.name and a.validFrom=b.mvf;
SELECT item[0] = {0:0}
item[1] = {0:1}
item[2] = {0:2}
FROM {0,*} = aliases (AS a)
{1,*} = SELECT agg_flag
item[0] = AGG{2:0}
item[1] = AGG_FUNCTION:max(AGG{2:2})
FROM {2,*} = aliases
WHERE LE({2:2},FUNCTION:strftime(item[0] = '%s'
item[1] = 'now'))
GROUPBY {2:0}
END (tabname=sqlite_subquery_53D2D0_) (AS b)
WHERE AND(EQ({0:0},{1:0}),EQ({0:2},{1:1}))
END
sele order from deta
---- ------------- ---- ----
1 0 0 SCAN TABLE aliases (~333333 rows)
1 0 0 USE TEMP B-TREE FOR GROUP BY
0 0 1 SCAN SUBQUERY 1 AS b (~100 rows)
0 1 0 SEARCH TABLE aliases AS a USING AUTOMATIC COVERING
INDEX (name=? AND validFrom=?) (~7 rows)
sqlite>
-vs-
sqlite> explain query plan select a.name, a.assignedTo, a.validFrom from
aliases as a join (select name, max(validfrom) as mvf from
aliases where validFrom <= (select strftime('%s', 'now')) group by name) as b
on a.name=b.name and a.validFrom=b.mvf;
SELECT item[0] = {0:0}
item[1] = {0:1}
item[2] = {0:2}
FROM {0,*} = aliases (AS a)
{1,*} = SELECT agg_flag
item[0] = AGG{2:0}
item[1] = AGG_FUNCTION:max(AGG{2:2})
FROM {2,*} = aliases
WHERE LE({2:2},(SELECT FUNCTION:strftime(item[0] = '%s'
item[1] = 'now')
LIMIT 1
END))
GROUPBY {2:0}
END (tabname=sqlite_subquery_53E4D0_) (AS b)
WHERE AND(EQ({0:0},{1:0}),EQ({0:2},{1:1}))
END
sele order from deta
---- ------------- ---- ----
1 0 0 SCAN TABLE aliases (~333333 rows)
1 0 0 EXECUTE SCALAR SUBQUERY 2
1 0 0 USE TEMP B-TREE FOR GROUP BY
0 0 1 SCAN SUBQUERY 1 AS b (~100 rows)
0 1 0 SEARCH TABLE aliases AS a USING AUTOMATIC COVERING
INDEX (name=? AND validFrom=?) (~7 rows)
sqlite>
as you can see, the scalar subquery is executed only once ... (if it were a
correlated suquery, then it would be executed for each row, and the plan would
indicate that).
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
