1

In my app I need to display search results. It fetches the first 250 results, but also needs to display the total number of results.

This slows everything down, because I can't just stop fetching results after the first 250 rows, I need to loop through them all (millions) just to count them.

Another approach is to exit the loop after the first 250 rows, and do a second COUNT() query after that. But some quick benchmarking showed that the COUNT() query doesn't take advantage of the fact that it has executed the same query before.

Is there any way to optimize a scenario like this? Would it help if I combined the counting and the fetching in one query instead of two seperate ones? And if so, how would I do that?

Edit: Sorry, duplicate of How do I count the number of rows returned in my SQLite reader in C#?

Community
  • 1
  • 1
Maestro
  • 9,046
  • 15
  • 83
  • 116
  • 1
    Show us your query. Are you performing the count() on a column with an index or just doing count(*)? Try a count(my_indexed_column). Counting using an index might be faster but needs to be benchmarked. – bot403 Sep 26 '11 at 14:46
  • http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.htm – sehe Sep 26 '11 at 14:46
  • http://maxradi.us/documents/sqlite/ – sehe Sep 26 '11 at 14:47
  • @bot403 I'm doing COUNT() on the rowid, so it has an index. – Maestro Sep 26 '11 at 14:54

1 Answers1

0

If this is within a single table, you can use a trigger to keep the count updated.

Each insert trigger increments the count; each delete trigger decrements it.

See this similar SO question & answer

Community
  • 1
  • 1
Doug Currie
  • 40,708
  • 1
  • 95
  • 119
  • That's only possible if you have a fixed query. But these rows are search-results using LIKE% queries. I don't know what the user is going to enter in advance. – Maestro Sep 26 '11 at 15:50