On 04/11/2011 05:32 PM, Enrico Thierbach wrote:
>
> On 11.04.2011, at 10:18, Maxim V. Shiyanovsky wrote:
>
>> Does SQlite (3.6.23.1 to be precise) perform any optimization when user
>> function appears in the statement?
>>
>> I defined custom function MY_FUNC(a TEXT, b TEXT) and bound it with
>> sqlite3_create_function.
>>
>> Suppose, we have 2 tables:
>> Tbl1(id1 INTEGER, id2 INTEGER, str TEXT) with 2 records
>> Tbl2(id INTEGER, Tbl1_id INTEGER, str TEXT) with 5000 records
>>
>> Using query:
>> SELECT Tbl2.id, MY_FUNC(Tbl1.str, Tbl2.str) FROM Tbl2 JOIN Tbl1 ON
>> Tbl1.id1=Tbl2.Tbl1_id WHERE Tbl2.id>1000 AND Tbl1.id2=1 LIMIT 50
>> I hope MY_FUNC would be evaluated 50 times at most, but it appeared 1.
>>
>> Using subquery does not do anything with the problem.
>
> SELECT sq.id, MY_FUNC(sq.str1, sq.str2) FROM
>(SELECT Tbl2.id, Tbl1.str AS str1, Tbl2.str AS str2 FROM Tbl2 JOIN Tbl1 ON
> Tbl1.id1=Tbl2.Tbl1_id WHERE Tbl2.id>1000 AND Tbl1.id2=1 LIMIT 50) sq
>
> really doesn't help?
This doesn't seem right. What is the whole database schema? Is this
the exact query in use or a modified version?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
