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? /eno _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
