Re: [sqlite] Using FTS for '='

On 19 Feb 2012, at 3:39pm, Mohit Sindhwani <[EMAIL PROTECTED]> wrote:

> We have a table that has a number of fields (category, type, format, etc.) 
> all of which are integers and we have a text field that we need to search 
> for.  So, what we want to do is a query like:
> select * from myTable where text_field = '...' and category = 2;
> 
> Currently, to support this, we have indexes such as
> category, text_field
> type, text_field,
> etc.

You may get only marginally slower results by having just an index on 
text_field.  It depends on how 'chunky' your values are: whether you have lots 
of rows with the same value in [text_field] but different values in [category], 
for example.  Try some timing comparisons of the current setup with one after 
deleting all those indexes and creating just one for text_field.

But the great advantage of using FTS is in searching text fields for contents.  
If you're searching for exact contents of a text field FTS is a waste of time 
and filespace.

Simon.
_______________________________________________
sqlite-users mailing list
[EMAIL PROTECTED]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users