Hi, Dinu, I'll have to leave it to those more knowledgeable to respond to other aspects of your question, but I was struck by the following.
1) Regarding columns such as "provider TEXT(2000000000)" Do you really mean that values in such columns might need to contain up to two giga-characters (2 * 10**9)? Sqlite will simply interpret this as "TEXT" plain and simple -- but if your actual data contains monstrously-long values, AND these are part of primary keys, then I can imagine this would disastrously affect performance. 2) Complexity. I understand you said could not achieve good performance with straightforward joins. I wondered if it might be helpful to post your cleanest, simplest JOIN-based SELECT (and indexes and query plan) that you thought should have good performance, yet does not. 3) Analyze. You've likely already run the ANALYZE command, right? (A one-time run of VACUUM wouldn't hurt either, I suppose.) 4) Regarding: "CREATE INDEX sqlite_autoindex_materializedInstances_1 ON materializedInstances (provider,instanceId); Am I right that since (provider,instanceId) is already PRIMARY KEY, then this index is redundant? (Even so, I would only expect it to take up disk space and slow down inserts/deletes, but not slow down SELECTs.) Donald
