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
