On 8 Apr 2011, at 2:34pm, Vadim Smirnov wrote: > Tbl1(id1 INTEGER, id2 INTEGER, str TEXT) 2 records > Tbl2(id INTEGER, Tbl1_id INTEGER, str TEXT) ~5000 records > And SQL-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 > We expected that MY_FUNC would be evaluated 50 times at most, but it appeared > 10000! > Why so?
Your SELECT consists of a JOIN which tells SQLite to try every combination of one record from each table. For each combination it checks to see that all the conditions you gave are true. Since you have 2 records in one TABLE and ~5000 records in the other, that gives 10000 times it needs to run the tests. Try creating an index on id1 for Tbl1. Also try creating an index on id2 for Tbl1. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
