[Prev] Thread [Next]  |  [Prev] Date [Next]

Re: [sqlite] Using FTS for '=' Simon Slavin Sun Feb 19 09:00:24 2012

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.

sqlite-users mailing list