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]
