Hey Clemens. Thank you for your reply! > Pavlo wrote: > > What I have is one big table storing all mandatory properties of an entity > > and several small tables, storing optional properties. > > > > So, when I need to select big number of elements using JOIN the probability > > of optional properties is low. > > And so, for every row from big table there will be ~99% of join lookups in > > small tables leading to NULLs. > > > > So I got an idea to store flags in big table telling me whether optional > > property is present (flag). > > I want to lookup small tables only when flag is set but I can't see how to > > tell SQLite to skip JOIN lookup for certain rows and return NULLs. > > SELECT * FROM Big LEFT JOIN Small ON Big.flag AND Big.ID = Small.BigID > > However, for small tables, the IDs will be in the cache anyway. > It is unlikely that this will make any noticeable difference. >
Sadly, you are wrong here. Taking your query sqlite will Look for record anyway, and flag 'false' doesn't stop it! Personally I can't understand why it is not so smart to see that Big.flag does not depend on small table and so when it equivalent to 'false' there can't be any row in Small to match this condition... To prove I am gonna join Big to itself (to exclude the possibility of neglectable impact on query time of Small): $ time echo "SELECT Big.flags, B.flags X FROM Big LEFT JOIN Big B ON Big.id = B.id;" | sqlite3 database > /dev/null real 0m1.885s user 0m1.832s sys 0m0.044s $ time echo "SELECT Big.flags, B.flags X FROM Big LEFT JOIN Big B ON Big.flags & 0 AND Big.id = B.id;" | sqlite3 database > /dev/null real 0m1.991s user 0m1.952s sys 0m0.028s As you can see, second version even bit slower. Extra condition 'Big.flags & 0' is the reason for that... About the cache. It is not a true for us. Our specific project includes the operation with ~million of such databases per server. All data will never fit the FS cache. So we have to fight for as little disc OPs as possible. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
