CODEWITHSUNDEEP
iossqlitexamarin.ios
Brandon
Brandon

Reputation: 889

SQLite slow query on iPad

I have a table with almost 300K records in it. I run a simple select statement with a where clause on an indexed column ('type' is indexed): 

SELECT *
FROM Asset_Spec
WHERE type = 'County'

That query is fast - about 1 second. Additionally I want to test against status:

SELECT *
FROM Asset_Spec
WHERE type = 'County'
AND status = 'Active'

The second one is VERY slow (minutes). Status is NOT indexed and in this particular case 99.9% of values in the db ARE 'Active'.

Any ideas how I can get better performance? We are compiling our own version of SQLite so I can tweak many settings (FYI - same performance on iOS pre-canned SQLite)

Upvotes: 0

Views: 518

Answers (3)

Brandon
Brandon

Reputation: 889

I looked at the query plan and the estimate for number of rows was off by an astounding amount. Asset_Spec (~2 rows) - actual number of rows is almost 300,000. Ran 'ANALYZE' - now the same query runs in 16ms.

Upvotes: 2

theglauber
theglauber

Reputation: 29595

Any reason you need to select *?

Suggestions:

Do you need to retrieve multiple records? If all you need is the first record found, then add "limit 1" to the end of the query.

If you're just checking for the existence of a row, i.e. you only need to know that there is one row with status active, then "select 1" instead of "select *".

Upvotes: 0

Jason
Jason

Reputation: 89082

the first thing I would try is using a subquery

SELECT * FROM
(SELECT *
FROM Asset_Spec
WHERE type = 'County')
WHERE status = 'Active'

and as Robert suggests, adding an index on any column you want to filter by is a good idea. I'd also consider changing fields Type and Status to be something other than string.

Upvotes: 1

Related Questions