Re: Fulltext Relevancy not returning anticipated results?
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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
*
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext Relevancy not returning anticipated results?
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fulltext Relevancy not returning anticipated results?
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 l
RE: Fulltext Relevancy not returning anticipated results?
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]
> *
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
*
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext Relevancy not returning anticipated results?
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
FULLTEXT relevancy
Couple problems here...
Have a database with 36,316 emails archived.
emailarchive.emailbody has a FULLTEXT index.
If I query on a term that exists in the majority of the emails, it returns 0
rows and takes an incredible amount of time.
mysql> SELECT count('') FROM emailarchive where listnum = 1 and match
(emailbody) against ('shawn') group by'';
Empty set (7.19 sec)
The above query should return a total of 22,778.
mysql> SELECT count('') FROM emailarchive where listnum = 1 and match
(emailbody) against ('mysql') group by'';
+---+
| count('') |
+---+
| 121 |
+---+
1 row in set (0.03 sec)
Change nothing but the word you are searching on and it's relatively fast
(although it gets slower the more instances is there are)
mysql> SELECT count('') FROM emailarchive where listnum = 1 and match
(emailbody) against ('billing') group by'';
+---+
| count('') |
+---+
| 5062 |
+---+
1 row in set (1.43 sec)
The only thing I can think of is that the relevancy ranking causing both
problems (null set and speed problems) as it gets slower and slower the more
results there are until finally it just stops returning anything (maybe
there are so many the relevancy is zero?)
If that is the case, is there anyway to turn off the relevancy ranking
portion altogether or any suggestions to make it not so painfully slow? 7+
seconds for an "indexed" search on 36,000 records seems a little excessive
(the FileMaker database that we are upgrading from does the 'shawn' search
in 0.42 seconds and returns the correct results).
- Shawn
---
Shawn D. Hogan
President, Digital Point Solutions
http://www.digitalpoint.com
(858) 452-3696
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
