CODEWITHSUNDEEP
androidsqlsqliteoptimization
Nick
Nick

Reputation: 6385

Which query is more efficient?

I currently have two tables called 'names' and 'accounts'. I need to select all the names where the account is enabled. So I can do this one of two ways but I am wondering which would be faster and most efficient?

The first way is to select all accounts that are enabled, then in a separate query have a where clause with these accounts (pseudocode):

    SELECT account_id FROM accounts WHERE enabled=1;

then

    SELECT * FROM names WHERE account_id IN (account_id_list);

OR I can do a simple join on the tables:

    SELECT * FROM names JOIN accounts USING account_id WHERE enabled=1;

The name table will be around 10-25k rows and will also be running on a mobile device (Android) using SQLite.

Thanks!

Upvotes: 1

Views: 160

Answers (3)

JamieSee
JamieSee

Reputation: 13010

SELECT * FROM names WHERE EXISTS (SELECT 1 FROM accounts WHERE account_id = names.account_id AND enabled=1);

Upvotes: 0

user1096188
user1096188

Reputation: 1839

nested selects are MUCH better than joins, so just combine first two queries into one:

SELECT * FROM names WHERE account_id IN (
SELECT account_id FROM accounts WHERE enabled=1);

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 180897

Considering that the query is quite simple and a lot of the work the SQL engine has to do in the first case is just formatting/passing data to your program just to have it sent back right away and have to parse it again, the second version should be quite a bit more efficient (and save your program some work too)

Upvotes: 0

Related Questions