Re: [sqlite] Implementation check request
> > On 28 Aug 2010, at 4:24pm, Max Vlasov wrote: > >> I don't know whether my posts have a delay with delivery (replied >> several >> hours ago to the discussion you mentioned), but actually I tested 100 >> rows >> before and after with a similar query (ORDER BY LIMIT) and it definitely >> shows that sqlite saves time and doesn't go further. Do you have reason >> to >> think that it should go to the end? > > Thanks Max. There was a thread where someone suggested the opposite: that > the LIMIT clause operated to trim results which had already been found. > It worried me because I have been assuming that LIMIT meant that SQLite > didn't have to search the entire table. I think it depends of course if there is an index on the column that is specified in the ORDER BY. Without an index there is no way to limit the searched rows prior applying the sorting. So a simple SELECT * FROM T ORDER BY A LIMIT 10 will be fast with an index on A and slow without. Even if ORDER BY A DESC is specified sqlite still can scan the index. At least that shows a quick experiment using the sqlite3.exe. Marcus > > Simon. > ___ > 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
Re: [sqlite] Implementation check request
On 28 Aug 2010, at 4:24pm, Max Vlasov wrote: > I don't know whether my posts have a delay with delivery (replied several > hours ago to the discussion you mentioned), but actually I tested 100 rows > before and after with a similar query (ORDER BY LIMIT) and it definitely > shows that sqlite saves time and doesn't go further. Do you have reason to > think that it should go to the end? Thanks Max. There was a thread where someone suggested the opposite: that the LIMIT clause operated to trim results which had already been found. It worried me because I have been assuming that LIMIT meant that SQLite didn't have to search the entire table. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implementation check request
Simon, I don't know whether my posts have a delay with delivery (replied several hours ago to the discussion you mentioned), but actually I tested 100 rows before and after with a similar query (ORDER BY LIMIT) and it definitely shows that sqlite saves time and doesn't go further. Do you have reason to think that it should go to the end? Max On Sat, Aug 28, 2010 at 7:03 PM, Simon Slavin wrote: > Can someone check the source code for me ? Or perhaps this can be found > from a query plan or something. > > A current discussion reminded me that something was never settled to my > satisfaction. If I use LIMIT in a SELECT, e.g. > > SELECT name,address,(residents-1) FROM contacts WHERE postcode>'N" ORDER BY > postcode LIMIT 100 > > and I do actually have an index on the postcode column, does SQLite process > all the rows to the end of the index, then trim them to the first 100 rows, > or does it realise it needs only the first 100 rows ? > > If the question doesn't make sense, imagine that my 'contacts' table has > ten million rows in. I'm trying to work out whether it'll take a lot of > time. > > Simon. > ___ > 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
[sqlite] Implementation check request
Can someone check the source code for me ? Or perhaps this can be found from a query plan or something. A current discussion reminded me that something was never settled to my satisfaction. If I use LIMIT in a SELECT, e.g. SELECT name,address,(residents-1) FROM contacts WHERE postcode>'N" ORDER BY postcode LIMIT 100 and I do actually have an index on the postcode column, does SQLite process all the rows to the end of the index, then trim them to the first 100 rows, or does it realise it needs only the first 100 rows ? If the question doesn't make sense, imagine that my 'contacts' table has ten million rows in. I'm trying to work out whether it'll take a lot of time. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
