> It seems legitimate to use the idx_foobar because it is already > sorted.. no?
Yes, it is sorted. So for example you have 2 values and you need to put them in order. You know that these values exist somewhere in the index in exact order you need. How would you find the order? You'll need to scan the whole index to find values you have (you don't know where exactly they are in the index) and then you'll know what order they have. And you have to repeat this index scan for each value you have. Do you think it would be faster than just calling your comparison function? Pavel On Wed, Oct 20, 2010 at 10:30 AM, Mathieu Schroeter <schroe...@epsitec.ch> wrote: > Le 20.10.2010 15:51, Drake Wilson a écrit : >> Quoth Mathieu Schroeter<schroe...@epsitec.ch>, on 2010-10-20 15:41:51 +0200: >>> Thanks for your interesting answer. >>> >>> The first plan looks the best but it does not explain the ORDER BY. >>> If I could done this job with pencil and paper, my way will be: >>> >>> a) enumerate all entries in lol >>> b) for each entry look up a corresponding row in tmp using its >>> primary key >>> c) the loop over lol is complete, I see that tmp.data uses >>> `COLLATE foobar` then I look if an index exists. >>> d) Yes then I use idx_foobar instead of many calls on the >>> comparison function. >> >> In step (d), you're doing what? Sorting the resulting rows? How >> exactly would you use the index for that? >> > > Well, I don't know how are implemented the indexes. I know only > that the index (idx_foobar) is ordered and this one is populated > at the same time that the INSERT. > > CREATE INDEX idx_foobar ON tmp (data COLLATE foobar); > > INSERT INTO tmp (data) values (CAST(x'0100' AS TEXT)); > INSERT INTO tmp (data) values (CAST(x'0400' AS TEXT)); > ... > > and CAST() is used because I have a BLOB in my table... > > > I would like to save time for this query (no index for lol.id): > > SELECT data > FROM lol INNER JOIN tmp > ON lol.id = tmp.id > ORDER BY tmp.data COLLATE foobar; > > > Now each row in the result are using the comparison function provided > by foobar. But the comparisons were already done when the data were > inserted in the table?!? > > It seems legitimate to use the idx_foobar because it is already > sorted.. no? > > > Maybe I've not understood what is an index. > > > Mathieu > > > _______________________________________________ > 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
