According to my understanding of standard SQL, you should be able to say:
SELECT arbitrary_expression() AS bar FROM foo ORDER BY bar;
... and the expression is only evaluated once per row, not twice.
Your actual example seems confusing, since you appear to alias your
'vectors' table to 'match' in the from clause, which is also the name
of your function, and the name of what you sort by. Perhaps having
different names for each thing that is actually different will make
your question easier to answer.
For example:
SELECT uid, match_func("complex", "function", vector) AS match_res
FROM vectors AS match_tbl
ORDER BY match_res DESC
LIMIT 20;
-- Darren Duncan
At 10:01 PM -0700 11/12/05, Nathan Kurz wrote:
Hello --
I'm trying to figure out how to optimize a query a bit, and think I've
hit a case that could easily be optimized by sqlite but isn't. I'm
wondering if it would be an easy optimization to add, or whether there
is some way I can 'hint' the optization into being.
I'm using a computationally expensive user defined function called
'match()'. In case it makes a difference, match() is written in C,
and for testing, I'm loading it as a shared library into the sqlite3
shell application. I want to return the value of match(), and also
order by it. So my query looks something like this:
SELECT uid, match("complex", "function", vector) FROM vectors AS match
ORDER BY match DESC LIMIT 20;
I had expected that match() would only be called once per row, but it
turns out to be called twice: once for the select, and once for the
ordering. I've confirmed this both by putting in a counter, and by
using 'EXPLAIN'. Is there any way to tell SQLite to reuse the value
of the first call rather than calling the function again?
I'm a comfortable C programmer, but only superficially familiar with
the SQLite code so far. If I'm not missing something obvious, hints
on where to look at writing a patch for this would be appreciated.
Thanks!
Nathan Kurz
[EMAIL PROTECTED]
