Hi I have two queries. Each returns a full result set in around 1 minute when run on there own. Making each of these queries a subquery and joining them with an inner join, and the query takes more than 10 minutes. I cancelled it after 10 minutes.
Does anyone have any ideas on how to optimise this type of process in SQLite? Indices only make things worse. I have read through most of the threads on this and it still seems unclear as to when and why indices chosen seem to be worse. Analyse always seems to make things worse. So does adding indices. I have a two tables: one wide and low on records and one thin and having around 4 million records. Withonly UNIQUE PK indices, the queries complete in around 1 minute usually. With an index that satisfies the group by, it takes over 10 minutes and still doesnt complete. Analsye makes sure it uses the group by index as well. To get best performance I seem to have to clear all indices and the sqlite_stat1 table. So I am at a loss as to how to take two 1 minute queriues and join them in an efficient manner as subqueries. If I make the first subquery into a temp table then do join the temp table to the second query, it completes in 1.5 minutes. I would expect the same performance from subqueries. Thanksm _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
