On Tue, Sep 9, 2008 at 9:04 AM, Bruno Moreira Guedes <[EMAIL PROTECTED]>wrote:
> 2008/9/9 P Kishor <[EMAIL PROTECTED]>: > > On 9/9/08, Bruno Moreira Guedes <[EMAIL PROTECTED]> wrote: > >> Hi people!! > >> > >> I'm getting problems with a bigger table. I'm doing a query like this: > >> > >> SELECT field FROM tables WHERE '%.' || somefield LIKE 'somestring' > > > > interesting variation on the syntax. Usually one would > > > > WHERE somefield LIKE '%.somestring' > > > > > > Yes... I need to get the rows where 'something' ENDS wifh > '.[field-value]'. But I really think this solution isn't very well. Store the field *twice* -- once normally, and once *backwards*. Put an index on the backwards column, and when searching, do: somefield_backwards >= 'gnitset' and somefield_backwards < 'gnitseu' (Note that I advanced the last character from 't' to 'u' and used < instead of <=) This will enable SQLite to use the index on the backwards column to efficiently find everything. Idea: Submit a patch that allows LIKE expressions that start with a fixed string (i.e. don't start with '%') to use the index to improve performance. (SQL Server 2000 does this.) Idea 2: Submit a patch adding a built-in 'reverse' function to assist in this. -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
