On Fri, 7 Oct 2011 19:57:09 +0100, Simon Slavin <slav...@bigfraud.org> wrote:
>On 7 Oct 2011, at 7:17pm, Roger Binns wrote: > >> On 07/10/11 09:52, Simon Slavin wrote: >>> "Do you really want to see all 50,000 entries that that search would >>> return ?". If this kind of search returns more than 100 records, >>> there's no point in doing it at all. >> >> You can solve this at the user interface layer and there is no need to >> calculate how many matching records there are. >> >> Lets say you can show 50 results on the screen. Run the query but leave >> it open until hitting the 51st result. Show the 50 results on the screen >> with a More button. > > It's not quite the problem I have. My problem is that there is > a huge amount of data involved in finding the 50,000 matches > and merely processing the SELECT is very slow. And SQLite > has to find all 50,000 before it can show the first screen > of matches. > > Imagine you're watching _CSI_ on TV and they have a test that > identifies possible matches to something ... car colours and > tyres perhaps. If there are only 50 possible matches, > it's worth getting printouts and eliminating them one by one. > If there are 50,000 possible matches there's no point. > > My problem really seems to be with the way SQLite implements > LIMIT n. If I understand previous posts on this list correctly, > instead of finding only the first n records, it does the entire > search first, then returns the first n of the results it found. What the first n rows are is determined by the ORDER BY clause. > With a standard implementation I would simply do a search with > a LIMIT 51 clause. If 51 results are returned then I know the > search is useless. But even with a LIMIT clause SQLite will > still do all the fetching involved in all 50,000 records. Read about some optimization options on: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor -- ( Kees Nuyt ) c[_] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
