On 30 Nov 2013, at 5:40pm, Tristan Van Berkom <trista...@openismus.com> wrote:
> So, is there a way that I can tell SQLite forcibly to > prioritize the index on email_list.value when making > a prefix match ? Don't use LIKE or GLOB for prefix matches. Although you as a human can tell that >>> email_list.value LIKE 'eddie%' is a prefix match, all the computer sees is pattern-matching. This makes it try all the available combinations. Instead use email_list.value BETWEEN 'eddie' AND 'eddie}' (I chose the close curly bracket because it has a very high code and will sort near the end of the possibiliites.) Using BETWEEN allows SQLite to use any available index for searching and sorting. In this case it's equivalent to saying "We don't have to look through all 120 pages of this book, we need only pages 34 to 49.". In the case of the SELECT you show, modified for my suggestion, a good index would be something like CREATE INDEX fiel_uv ON folder_id_email_list (uid, value) This allows a specific match on a uid and then partial matching on value, which is what it wants. This can replace the existing index you mention. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
