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.
- SELECT * FROM table1 ORDER BY indexedField LIMIT 1
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.
- SELECT table1.*, (SELECT * FROM table2 WHERE table2.someField=table1.someField) FROM table1 ORDER BY indexedField LIMIT 1
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!
New tutorials from Helloandroid
Recent Apps
Android on Twitter
-
@Idevicegazette (iDevice Gazette)
GSM-to-Skype bridge lets you lose those roaming fees http://bit.ly/lbRJeh #android
11 years 37 weeks ago -
@tommy_banane (tom b.)
RT @AndroidFavorite: #Android New Desktop Android Market Is Live, Adds Several New Features http://zorr0.nl/lFwXNz
11 years 37 weeks ago -
@dwilliams5 (Dennis Williams)
just completed a runtastic run of 3.02 km in 40 min 11 s with #runtastic #Android App: http://tinyurl.com/5tvrpe3
11 years 37 weeks ago -
@S_Pinz (Spinz!)
RT @Androidheadline: Out of box #LG Optimus 3D got Quadrant 2420 score. Thanks @blink_c #io2011 #android http://twitpic.com/4whkdz
11 years 38 weeks ago -
@tayaitapps (Taya IT)
Next Google TV Looks A Lot Like Android http://t.co/dvlTim3 via @alleyinsider #google #apple #android #tv #honeycomb
11 years 38 weeks ago
Poll
Useful resources
Android Development Projects
- iOS/Android Developer to take older Games and bring them Current
- Android apps developer - need to finish urgent.
- Buliding MobileApp For onlie order
- looking for android APP developers
- Create an ecommerce app
- text-to voice for smartphones IOS - GOOGLE - HARMONY - AND ALEXA
- Optimize Images on App
- Create small feature with drag-drop text for Android
- Scouting for advanced website and Mobile apps developers. Potential Long-term contract.
- BLACK SCREEN