On Thu, Nov 24, 2005 at 01:35:56AM +0100, Michael Scharf wrote: > -- The user sorts on NUMBER and selects NAME that > -- start with 'foo'. I create a temp table view1.
Are you always using a LIKE pattern that starts with a fixed string, or is the wildcard sometimes first? If it's always fixed, your query should probably be very fast if you are searching on a properly indexed field. Check out http://www.sqlite.org/optoverview.html under "4.0 The LIKE optimization" and see if there is a way to get your query to fit that model: name >= 'foo' and name < 'fop'. > This work fine currently. The question is, is this an artifact > of the current implementation or can I rely on this in the > future? (I know that's not compatible with the SQL specs) I can't speak to future development, but from my recent looking at the code involved I think it would be hard to have the rows inserted in any order than that of the index that is used. So for an embedded app where you can test before any new versions, I wouldn't worry too much about it changing. On the other hand, I would try hard to come up with a fast version that doesn't need to rely on a temp table. Good luck, --nate
