Pavel,
> No SQL format can guarantee anything. And changing SQL format won't change
> its execution plan - SQL optimizer will decide that for you.
Agreed.
> And I don't know where did you take your information from but I can guarantee
> you that with your query SQLite will execute strftime many times and OP's
> problem will persist.
You are incorrect. When the strftime function is used in either a scalar
subquery or a joined table query, it is executed exactly once. In the original
query it was executed multiple times, however, making it a scalar subquery
fixes that.
There are many ways to phrase the query, and if you force the appropriate
covering index on aliases (name, validFrom, assignedTo) they all come out with
just quite similar code. As long as "strftime('%s', 'now')" is replaced with
"(select strftime('%s', 'now'))" it will only be executed once. Whether you
unravel the query by hand or let the optimizer do it, you get almost the same
result in all cases, though some queries will be more efficient than others,
depending on how big the tables are (if they are tiny, then it matters not, if
they will always be tiny).
You can use .explain and explain (not explain query plan) and see that for
youself.
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
