I have a database of products, doing a search on them trying to achieve a
modicum of relevancy, but am getting a strange result on some returned rows:

QUERY:
select *,match(code) against ('vic*' IN BOOLEAN MODE) * 8 + match(name)
against ('vic*' IN BOOLEAN MODE) * 4 + match(small_desc) against ('vic*' IN
BOOLEAN MODE) * 2 + match(large_desc) against ('vic*' IN BOOLEAN MODE) as
score from products where active='y' and site like '%,1,%' and
match(code,name,small_desc,large_desc) against ('vic*' IN BOOLEAN MODE)
order by score desc

I get returned 6 rows from the product database. The strange thing is,
there is a product (at least one that is known about), with the name:
"Salchichon de Vic" that is NOT being returned as a result.

When I break out the scores, I get a match on the first three products
returned for the titles:

6-Pack Vichy Catalan Sparkling Spring Water
2-Pack Vichy Catalan Mineral Water
San Vicente - Tempranillo 2001

And the other three products have a match in the large_desc.

What is confusing me is why I am not getting any match on the titles for the
"Salchichon de Vic" - which by my thought process should be the most
relevant of all returns?

Any thoughts on this? The term "vic" used in this case is the search string
submitted by the user.

You can see the search in action at
http://www.tienda.com/support/search.html

--
Cheers

Mike Morton

************************************************** **
*
* Tel: 905-465-1263
* Email: mike@webtraxx.com
*
************************************************** **

By default, MySQL ignores any "word" with less than four characters when
doing full text searches. You can change this in my.conf, the setting should
be obvious.

I had to do this so that customers could find inventory items that were red.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com

> -----Original Message-----
> From: Mike Morton [mailto:mike@webtraxx.com]
> Sent: Monday, October 29, 2007 5:39 PM
> To: mysql@lists.mysql.com
> Subject: Fulltext Relevancy not returning anticipated results?
>
> I have a database of products, doing a search on them trying to achieve
> a
> modicum of relevancy, but am getting a strange result on some returned
> rows:
>
> QUERY:
> select *,match(code) against ('vic*' IN BOOLEAN MODE) * 8 + match(name)
> against ('vic*' IN BOOLEAN MODE) * 4 + match(small_desc) against
> ('vic*' IN
> BOOLEAN MODE) * 2 + match(large_desc) against ('vic*' IN BOOLEAN MODE)
> as
> score from products where active='y' and site like '%,1,%' and
> match(code,name,small_desc,large_desc) against ('vic*' IN BOOLEAN MODE)
> order by score desc
>
> I get returned 6 rows from the product database. The strange thing is,
> there is a product (at least one that is known about), with the name:
> "Salchichon de Vic" that is NOT being returned as a result.
>
> When I break out the scores, I get a match on the first three products
> returned for the titles:
>
> 6-Pack Vichy Catalan Sparkling Spring Water
> 2-Pack Vichy Catalan Mineral Water
> San Vicente - Tempranillo 2001
>
> And the other three products have a match in the large_desc.
>
> What is confusing me is why I am not getting any match on the titles
> for the
> "Salchichon de Vic" - which by my thought process should be the most
> relevant of all returns?
>
> Any thoughts on this? The term "vic" used in this case is the search
> string
> submitted by the user.
>
> You can see the search in action at
> http://www.tienda.com/support/search.html
>
> --
> Cheers
>
> Mike Morton
>
> ************************************************** **
> *
> * Tel: 905-465-1263
> * Email: mike@webtraxx.com
> *
> ************************************************** **
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
> infoshop.com

Jerry:

Sorry - I should have mentioned in my previous email that we have changed
that min. word length to 2 in the config already, as we have MANY 3 letter
searches...

Any other suggestions?


On 10/30/07 9:12 AM, "Jerry Schwartz" <jschwartz@the-infoshop.com> wrote:

> By default, MySQL ignores any "word" with less than four characters when
> doing full text searches. You can change this in my.conf, the setting should
> be obvious.
>
> I had to do this so that customers could find inventory items that were red.
>
> Regards,
>
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
> www.the-infoshop.com
> www.giiexpress.com
> www.etudes-marche.com
>
>> -----Original Message-----
>> From: Mike Morton [mailto:mike@webtraxx.com]
>> Sent: Monday, October 29, 2007 5:39 PM
>> To: mysql@lists.mysql.com
>> Subject: Fulltext Relevancy not returning anticipated results?
>>
>> I have a database of products, doing a search on them trying to achieve
>> a
>> modicum of relevancy, but am getting a strange result on some returned
>> rows:
>>
>> QUERY:
>> select *,match(code) against ('vic*' IN BOOLEAN MODE) * 8 + match(name)
>> against ('vic*' IN BOOLEAN MODE) * 4 + match(small_desc) against
>> ('vic*' IN
>> BOOLEAN MODE) * 2 + match(large_desc) against ('vic*' IN BOOLEAN MODE)
>> as
>> score from products where active='y' and site like '%,1,%' and
>> match(code,name,small_desc,large_desc) against ('vic*' IN BOOLEAN MODE)
>> order by score desc
>>
>> I get returned 6 rows from the product database. The strange thing is,
>> there is a product (at least one that is known about), with the name:
>> "Salchichon de Vic" that is NOT being returned as a result.
>>
>> When I break out the scores, I get a match on the first three products
>> returned for the titles:
>>
>> 6-Pack Vichy Catalan Sparkling Spring Water
>> 2-Pack Vichy Catalan Mineral Water
>> San Vicente - Tempranillo 2001
>>
>> And the other three products have a match in the large_desc.
>>
>> What is confusing me is why I am not getting any match on the titles
>> for the
>> "Salchichon de Vic" - which by my thought process should be the most
>> relevant of all returns?
>>
>> Any thoughts on this? The term "vic" used in this case is the search
>> string
>> submitted by the user.
>>
>> You can see the search in action at
>> http://www.tienda.com/support/search.html
>>
>> --
>> Cheers
>>
>> Mike Morton
>>
>> ************************************************** **
>> *
>> * Tel: 905-465-1263
>> * Email: mike@webtraxx.com
>> *
>> ************************************************** **
>>
>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
>> infoshop.com
>
>
>
>

--
Cheers

Mike Morton

************************************************** **
*
* Tel: 905-465-1263
* Email: mike@webtraxx.com
*
************************************************** **

Sorry, no. I hope someone else will be able to .

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com

> -----Original Message-----
> From: Mike Morton [mailto:mike@webtraxx.com]
> Sent: Tuesday, October 30, 2007 9:56 AM
> To: Jerry Schwartz; mysql@lists.mysql.com
> Subject: Re: Fulltext Relevancy not returning anticipated results?
>
> Jerry:
>
> Sorry - I should have mentioned in my previous email that we have
> changed
> that min. word length to 2 in the config already, as we have MANY 3
> letter
> searches...
>
> Any other suggestions?
>
>
> On 10/30/07 9:12 AM, "Jerry Schwartz" <jschwartz@the-infoshop.com>
> wrote:
>
> > By default, MySQL ignores any "word" with less than four characters
> when
> > doing full text searches. You can change this in my.conf, the setting
> should
> > be obvious.
> >
> > I had to do this so that customers could find inventory items that
> were red.
> >
> > Regards,
> >
> > Jerry Schwartz
> > The Infoshop by Global Information Incorporated
> > 195 Farmington Ave.
> > Farmington, CT 06032
> >
> > 860.674.8796 / FAX: 860.674.8341
> >
> > www.the-infoshop.com
> > www.giiexpress.com
> > www.etudes-marche.com
> >
> >> -----Original Message-----
> >> From: Mike Morton [mailto:mike@webtraxx.com]
> >> Sent: Monday, October 29, 2007 5:39 PM
> >> To: mysql@lists.mysql.com
> >> Subject: Fulltext Relevancy not returning anticipated results?
> >>
> >> I have a database of products, doing a search on them trying to
> achieve
> >> a
> >> modicum of relevancy, but am getting a strange result on some
> returned
> >> rows:
> >>
> >> QUERY:
> >> select *,match(code) against ('vic*' IN BOOLEAN MODE) * 8 +
> match(name)
> >> against ('vic*' IN BOOLEAN MODE) * 4 + match(small_desc) against
> >> ('vic*' IN
> >> BOOLEAN MODE) * 2 + match(large_desc) against ('vic*' IN BOOLEAN
> MODE)
> >> as
> >> score from products where active='y' and site like '%,1,%' and
> >> match(code,name,small_desc,large_desc) against ('vic*' IN BOOLEAN
> MODE)
> >> order by score desc
> >>
> >> I get returned 6 rows from the product database. The strange thing
> is,
> >> there is a product (at least one that is known about), with the
> name:
> >> "Salchichon de Vic" that is NOT being returned as a result.
> >>
> >> When I break out the scores, I get a match on the first three
> products
> >> returned for the titles:
> >>
> >> 6-Pack Vichy Catalan Sparkling Spring Water
> >> 2-Pack Vichy Catalan Mineral Water
> >> San Vicente - Tempranillo 2001
> >>
> >> And the other three products have a match in the large_desc.
> >>
> >> What is confusing me is why I am not getting any match on the titles
> >> for the
> >> "Salchichon de Vic" - which by my thought process should be the most
> >> relevant of all returns?
> >>
> >> Any thoughts on this? The term "vic" used in this case is the
> search
> >> string
> >> submitted by the user.
> >>
> >> You can see the search in action at
> >> http://www.tienda.com/support/search.html
> >>
> >> --
> >> Cheers
> >>
> >> Mike Morton
> >>
> >> ************************************************** **
> >> *
> >> * Tel: 905-465-1263
> >> * Email: mike@webtraxx.com
> >> *
> >> ************************************************** **
> >>
> >>
> >>
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
> >> infoshop.com
> >
> >
> >
> >
>
> --
> Cheers
>
> Mike Morton
>
> ************************************************** **
> *
> * Tel: 905-465-1263
> * Email: mike@webtraxx.com
> *
> ************************************************** **
>
>

OK - I am at a total loss here

We have added an addition fulltext field with the highest rating:
match(search_keywords) against ('vic*' IN BOOLEAN MODE) * 16

And verified that in that field, there is indeed a keyword "vic" - but still
- that result is not returned, SO...

That leads me to believe, that despite what my eyes see in the my.cnf:
ft_min_word_len = 2

Is indeed, not true.

So:
1. How do I confirm that when MYSQL starts up, it is indeed paying
attention the the file that I assume it is, /etc/my.cnf
2. How can I confirm whether this setting is confirmed as min word length
of 2, rather than the default 3?
3. Am I losing my mind that this search is not returning the result set
that I expect? Am I missing something in my query, is the search being
performed trying to match something I am not expecting?

I guess I am expecting the match to work similar to the like query, except
that it is looking for a string of "vic" somewhere within the field...

Perhaps it is something to do with the "*" appended on there? I thought
that as a wildcard operator it would match " vic ", "vichon", "vickey",
"abcvicdef" equally, but am I mistaken in that? If so, how can I replicate
a "like" type search with '%vic%' using fulltext, which is our intention?
The reason that I am using fulltext is:

1. I understand that is may be faster than a simple like search
2. The number of fields we are using for searching
3. The "relevancy" ranking that we are doing in this case

Am I wrong? I am really pushing the envelope of my MYSQL knowledge here, so
assistance is appreciated I am not adverse to reading documentation,
just sometimes confused by what I am reading

TIA!


On 10/30/07 10:12 AM, "Jerry Schwartz" <jschwartz@the-infoshop.com> wrote:

> Sorry, no. I hope someone else will be able to .
>
> Regards,
>
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
> www.the-infoshop.com
> www.giiexpress.com
> www.etudes-marche.com
>
>> -----Original Message-----
>> From: Mike Morton [mailto:mike@webtraxx.com]
>> Sent: Tuesday, October 30, 2007 9:56 AM
>> To: Jerry Schwartz; mysql@lists.mysql.com
>> Subject: Re: Fulltext Relevancy not returning anticipated results?
>>
>> Jerry:
>>
>> Sorry - I should have mentioned in my previous email that we have
>> changed
>> that min. word length to 2 in the config already, as we have MANY 3
>> letter
>> searches...
>>
>> Any other suggestions?
>>
>>
>> On 10/30/07 9:12 AM, "Jerry Schwartz" <jschwartz@the-infoshop.com>
>> wrote:
>>
>>> By default, MySQL ignores any "word" with less than four characters
>> when
>>> doing full text searches. You can change this in my.conf, the setting
>> should
>>> be obvious.
>>>
>>> I had to do this so that customers could find inventory items that
>> were red.
>>>
>>> Regards,
>>>
>>> Jerry Schwartz
>>> The Infoshop by Global Information Incorporated
>>> 195 Farmington Ave.
>>> Farmington, CT 06032
>>>
>>> 860.674.8796 / FAX: 860.674.8341
>>>
>>> www.the-infoshop.com
>>> www.giiexpress.com
>>> www.etudes-marche.com
>>>
>>>> -----Original Message-----
>>>> From: Mike Morton [mailto:mike@webtraxx.com]
>>>> Sent: Monday, October 29, 2007 5:39 PM
>>>> To: mysql@lists.mysql.com
>>>> Subject: Fulltext Relevancy not returning anticipated results?
>>>>
>>>> I have a database of products, doing a search on them trying to
>> achieve
>>>> a
>>>> modicum of relevancy, but am getting a strange result on some
>> returned
>>>> rows:
>>>>
>>>> QUERY:
>>>> select *,match(code) against ('vic*' IN BOOLEAN MODE) * 8 +
>> match(name)
>>>> against ('vic*' IN BOOLEAN MODE) * 4 + match(small_desc) against
>>>> ('vic*' IN
>>>> BOOLEAN MODE) * 2 + match(large_desc) against ('vic*' IN BOOLEAN
>> MODE)
>>>> as
>>>> score from products where active='y' and site like '%,1,%' and
>>>> match(code,name,small_desc,large_desc) against ('vic*' IN BOOLEAN
>> MODE)
>>>> order by score desc
>>>>
>>>> I get returned 6 rows from the product database. The strange thing
>> is,
>>>> there is a product (at least one that is known about), with the
>> name:
>>>> "Salchichon de Vic" that is NOT being returned as a result.
>>>>
>>>> When I break out the scores, I get a match on the first three
>> products
>>>> returned for the titles:
>>>>
>>>> 6-Pack Vichy Catalan Sparkling Spring Water
>>>> 2-Pack Vichy Catalan Mineral Water
>>>> San Vicente - Tempranillo 2001
>>>>
>>>> And the other three products have a match in the large_desc.
>>>>
>>>> What is confusing me is why I am not getting any match on the titles
>>>> for the
>>>> "Salchichon de Vic" - which by my thought process should be the most
>>>> relevant of all returns?
>>>>
>>>> Any thoughts on this? The term "vic" used in this case is the
>> search
>>>> string
>>>> submitted by the user.
>>>>
>>>> You can see the search in action at
>>>> http://www.tienda.com/support/search.html
>>>>
>>>> --
>>>> Cheers
>>>>
>>>> Mike Morton
>>>>
>>>> ************************************************** **
>>>> *
>>>> * Tel: 905-465-1263
>>>> * Email: mike@webtraxx.com
>>>> *
>>>> ************************************************** **
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=jschwartz@the-
>>>> infoshop.com
>>>
>>>
>>>
>>>
>>
>> --
>> Cheers
>>
>> Mike Morton
>>
>> ************************************************** **
>> *
>> * Tel: 905-465-1263
>> * Email: mike@webtraxx.com
>> *
>> ************************************************** **
>>
>>
>
>
>
>

--
Cheers

Mike Morton

************************************************** **
*
* Tel: 905-465-1263
* Email: mike@webtraxx.com
*
************************************************** **

Mike,

Mike Morton wrote:
> OK - I am at a total loss here
>
> We have added an addition fulltext field with the highest rating:
> match(search_keywords) against ('vic*' IN BOOLEAN MODE) * 16
>
> And verified that in that field, there is indeed a keyword "vic" - but still
> - that result is not returned, SO...
>
> That leads me to believe, that despite what my eyes see in the my.cnf:
> ft_min_word_len = 2

SHOW VARIABLES LIKE 'ft%';

Should show you the variables you care about.

VIOLA!

As it turns out, the cnf file that I was shown was the cnf file on the OLD
server that we used when we had DB/Webserver combined on one server - that
conf was not updated for the new separate DB server.

Thanks muchly all, that change made the search work as expected!!!

Thanks for the starting point for finding that mistake Baron!

*knocks head against wall repeatedly*



On 10/30/07 5:09 PM, "Baron Schwartz" <baron@xaprb.com> wrote:

> Mike,
>
> Mike Morton wrote:
>> OK - I am at a total loss here
>>
>> We have added an addition fulltext field with the highest rating:
>> match(search_keywords) against ('vic*' IN BOOLEAN MODE) * 16
>>
>> And verified that in that field, there is indeed a keyword "vic" - but still
>> - that result is not returned, SO...
>>
>> That leads me to believe, that despite what my eyes see in the my.cnf:
>> ft_min_word_len = 2
>
> SHOW VARIABLES LIKE 'ft%';
>
> Should show you the variables you care about.

--
Cheers

Mike Morton

************************************************** **
*
* Tel: 905-465-1263
* Email: mike@webtraxx.com
*
************************************************** **