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" <[EMAIL PROTECTED]> wrote:
> Sorry, no. I hope someone else will be able to help.
>
> 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:[EMAIL PROTECTED]
>> 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" <[EMAIL PROTECTED]>
>> 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:[EMAIL PROTECTED]
>>>> 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: [EMAIL PROTECTED]
>>>> *
>>>> ****************************************************
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> MySQL General Mailing List
>>>> For list archives: http://lists.mysql.com/mysql
>>>> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>>>> infoshop.com
>>>
>>>
>>>
>>>
>>
>> --
>> Cheers
>>
>> Mike Morton
>>
>> ****************************************************
>> *
>> * Tel: 905-465-1263
>> * Email: [EMAIL PROTECTED]
>> *
>> ****************************************************
>>
>>
>
>
>
>
--
Cheers
Mike Morton
****************************************************
*
* Tel: 905-465-1263
* Email: [EMAIL PROTECTED]
*
****************************************************
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
