CODEWITHSUNDEEP
javasqlrealm
SlaneR
SlaneR

Reputation: 714

Performance difference between given queries?

I'm trying to make a method that build a RealmQuery.

But here's my problem. I wonder what's performance difference between given queries?


1. Selecting all records with WHERE clause. 

SELECT * FROM table WHERE gender = 'M' OR gender = 'F';

Realm.where(SOME_CLASS.class)
    .equalTo("gender", "M")
    .equalTo("gender", "F")
    .findAll();


2. Selecting all records with WHERE ~ IN. 

SELECT * FROM table WHERE gender IN ('M', 'F');

Realm.where(SOME_CLASS.class)
    .in("gender", new String[] {"M", "F"})
    .findAll();


3. Selecting all records without WHERE clause. 

SELECT * FROM table;

Realm.where(SOME_CLASS.class)
    .findAll();

Note that column gender contain M or F only.
I think all of those queries return same result, but I want to know how does it works internally and what's performance between those queries.

Thanks for your interesting and sorry for my bad English.

Upvotes: 0

Views: 52

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522501

The following two conditions are functionally identical:

WHERE gender = 'M' OR gender = 'F'
WHERE gender IN ('M', 'F')

Both the first two queries would require checking the gender value for each record. Since every record is a match, it should not make much difference whether or not there is an index as the full table will be hit regardless. The third query, with no WHERE clause, also will do a full table scan, but it will not have to do any checking in the WHERE clause. For this reason, all other factors being the same, I would expect the third query to outperform the first two.

But the firm answer to your question can be had by analyzing these queries on the database using EXPLAIN or a similar tool.

Upvotes: 3

Related Questions