Ferret talk - Nov 2006 - non-searchable columns, normalization

Hello. I am new to Ferret. I am using it through Acts as Ferret.

Let''s say I have such a table, and all columns are indexed using the  
default behavior provided by acts_as_ferret:

ARTICLES
-id
-year
-body

[1] A typical request will be "select id from articles where KEYWORDS  
% body". Will id be indexed for fulltext searching? clearly the  
fulltext index on id will never be used... id is only in the index so  
that it can be returned.

[2] I anticipate that a response might be "because id is numeric and  
has a cardinality of 1/1, ferret is intelligent enough to store this  
efficiently in a b-tree and not waste space or time on a fulltext  
index". what about if there is a year column, against which we will  
never search?

[3] This is an acts_as_ferret-specific question: are there any  
convenient methods to do queries against the regular (mysql) database  
and the ferret database at the same time? for example, still using  
the example above, if year (and maybe several other columns) were NOT  
stored in the ferret DB, is there any level of abstraction at which i  
can query: "select id, year, author, title from articles where  
KEYWORDS % body" ?

thanks for any pointers.

john

> On Nov 29, 2006, at 7:16 PM, John Bachir wrote:
>
> [1] A typical request will be "select id from articles where  
> KEYWORDS % body". Will id be indexed for fulltext searching?  
> clearly the fulltext index on id will never be used... id is only  
> in the index so that it can be returned.
>
> [2] I anticipate that a response might be "because id is numeric  
> and has a cardinality of 1/1, ferret is intelligent enough to store  
> this efficiently in a b-tree and not waste space or time on a  
> fulltext index". what about if there is a year column, against  
> which we will never search?
Okay, I just discovered the :index option

http://ferret.davebalmain.com/api/classes/Ferret/Index/FieldInfo.html

So I answered the first 2 questions :)

John

Hi!

On Wed, Nov 29, 2006 at 07:16:22PM -0500, John Bachir
wrote:
> Hello. I am new to Ferret. I am using it through Acts as Ferret.
> 
> Let''s say I have such a table, and all columns are indexed using
the
> default behavior provided by acts_as_ferret:
> 
> ARTICLES
> -id
> -year
> -body
> 
[..]
> 
> [3] This is an acts_as_ferret-specific question: are there any  
> convenient methods to do queries against the regular (mysql) database  
> and the ferret database at the same time? for example, still using  
> the example above, if year (and maybe several other columns) were NOT  
> stored in the ferret DB, is there any level of abstraction at which i  
> can query: "select id, year, author, title from articles where  
> KEYWORDS % body" ?
You can give ActiveRecord conditions (and any other options such as
:include or :order) in the second parameter hash to find_by_contents:

Model.find_by_contents( query, {}, { :conditions => [''year >
?'',year] } )

but be aware that these conditions will be applied *after* searching the
Ferret index, and will further reduce the result set returned by ferret.
This makes Ferret''s :limit and :offset options quite useless. You can
however use these options inside the second hash so they will be applied
to the ActiveRecord find call.

cheers,
Jens


-- 
webit! Gesellschaft f?r neue Medien mbH          www.webit.de
Dipl.-Wirtschaftsingenieur Jens Kr?mer       kraemer at webit.de
Schnorrstra?e 76                         Tel +49 351 46766  0
D-01069 Dresden                          Fax +49 351 46766 66