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 10000. >> >> 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
