Hi Fabian, The problem is that the limit apparently is applied on the result set, and in this case the result set is only one row which is less than the 5000. The culprit is the count(*) which basically says that to get the first row in the result set all rows from the table has to be processed.
You could instead try: select count(*) from (select * from table limit 500) ; That would give you the number 500. But it is kind of meaningless. You might as well write: select * from table limit 500 ; And get the actual 500 first rows. In this case the result set is build up as the select statement processing loops through the records in the table, and thus it only needs to take 500 rows. Again, if you add a count(*) it needs to traverse all records to get the first and only row of the result set that states the number of records in the table. It is the same if you specify ORDER BY together with limit. You will only get the x number of records in the LIMIT statement but before that all records will have to be traversed and sorted before you the 500 rows, unless perhaps there already is an index with one component fitting the ORDER BY perfectly. What do you want to attain with the count? /Frank > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Fabian > Sent: 16 October 2011 19:09 > To: General Discussion of SQLite Database > Subject: [sqlite] Limit COUNT > > How can you limit a count-query? I tried: > > SELECT COUNT(*) FROM table LIMIT 5000 > > But it ignores the LIMIT clause. I think the workaround would be counting the > results of a sub-query, but I'm trying to understand whats wrong with the > syntax above. The goal is to make the engine stop iterating after it reached > the upper limit, in order to save performance. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
