CODEWITHSUNDEEP
performancesqlite
michelemarcon
michelemarcon

Reputation: 24767

Tip for sqlite count performance

I want to show an html page of the results of a sql query. My database can get to 500K record and beyond, but I'm running an ARM9 CPU. Therefore, if I run a

select count (*) where timeStamp < '2013-05-01'

I have to wait about 30sec. before having an answer. I think I need to do this query because I want to show some kind of pagination of the results and therefore I need to know how many records matches the requested criteria. Is this the only way? Is there any tip for speeding things up?

EDIT: clarified the query

Upvotes: 0

Views: 1107

Answers (1)

Dan Puzey
Dan Puzey

Reputation: 34198

In response to your comment: yes - it sounds like you're missing an index!

Searching or sorting on a column in any database is much improved when said column is indexed. Indexing stores extra information about the data in a column so that the data in it is more quickly traversed.

You will want to run Sql something like this to create your index:

CREATE INDEX ix_tableName_columnName ON tableName (columnName ASC)

Upvotes: 1

Related Questions