SQLite subqueries

Recently we worked with big databases under android, and during that I met query optimization problems when using subqueries.

From my previous experiences with MySQL I thought, the database engine optimization is always the best, and there is no way, that multiple queries from program code is better, but I ended running multiple selects in a loop in this case for the best result.

The problem appears when using a subquery and a LIMIT clause together. Lets have example queries with tables named table1 and table2.

The query above runs much faster than the same query without a limit clause. However if we add a subquery like below, the case seems different.

The query above takes the same time to run like the limit clause were not here. With tables containing lots of records it can make the query run forever!

But if I run a query without the subquery, then get the value from it by java code, and construct the subquery with it, and run it individually, both queryes are executed in no time.

With big tables, when you limit the query more than 1 rows it still worth to run multiple queries in a loop for each row, than using a real subquery.

If you know a better solution, or you see what did I wrong, fell free to share us!