Search code examples
androidsqlitesqlite-net-pcl

Select exists vs count

I am using Sqlite-net-pcl. I need to which query is better to use. I am searching in a table if there is at least one record.

First query

Select exists(Select 1 from invnentory where itemname='box2')

Second query

Select count(*) from inventory where itemname='box2'

Both queries are working right. But which is the best approach to go for sqlite-net-pcl?

Solution

  • This query:

    Select count(*) from inventory where itemname = 'box2'

    normally must do a full table scan to return the number of rows that satisfy the condition in the WHERE clause.

    But this:

    Select exists(Select 1 from invnentory where itemname='box2')

    will return as soon as it finds the 1st row that satisfies the condition and it will do a full table scan only if there isn't such a row.

    So EXISTS should perform better.