Cheers. I love to use SQLite engine and I am performance paranoid. Today I had an idea how to optimize complex query with JOINs.
What I have is one big table storing all mandatory properties of an entity and several small tables, storing optional properties. If for example big table may have 1M records, the small ones will have ~10k of them. 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. I found the way actually: to use CASE + sub-SELECTs instead of JOIN. But if small tables have a list of columns I have to do separate sub-SELECT for each column leading to needless overhead (well, it's still better than original approach). Paul _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
