It is easy to get around this limitation:
for each column containing indexable content, create a shadow column
containing content prepared for FULLTEXT indexing.
I usually do the following:
- for each word:
- replace all non whitespace, non-blank characters by "_"
- right pad to 4 with "_"
for example "B.M.W." become "B_M_W_", "tax" will become "tax_"
now you prepare similarly the string to MATCH
actually doing so is good personal hygiene regardless of the value of
ft_max_len set by the host.
it is mandatory when you deal with languages which do not separate words by
whitespaces (in my case Japanese)
HTH
Ignatius
_________________________
----- Original Message -----
From: "Gavin Amm" <Gavin@xxxxxxxxxx>
To: "Ignatius Reilly" <ignatius.reilly@xxxxxxx>
Sent: Wednesday, April 28, 2004 10:03 AM
Subject: RE: MySQL - counting number of instances of a word in a
field
Yes,
They do.
Unfortunately the site is hosted by an ISP & has set the indexed words
to >3, the site requires essential words of 3 char length to be searched
through, such as "tax", "ato", "cgt", etc...
I will certainly still index the 2 main columns in the table...
Thanks for your help.
-----Original Message-----
From: Ignatius Reilly [mailto:ignatius.reilly@xxxxxxx]
Sent: Wednesday, 28 April 2004 4:34 PM
To: Gavin Amm; php-db@xxxxxxxxxxxxx
Subject: Re: MySQL - counting number of instances of a word in
a field
The functions do not exist.
However, FULLTEXT indexes will do much of this work for you. I encourage
you
to experiment with them.
_________________________
----- Original Message -----
From: "Gavin Amm" <Gavin@xxxxxxxxxx>
To: <php-db@xxxxxxxxxxxxx>
Sent: Wednesday, April 28, 2004 7:18 AM
Subject: MySQL - counting number of instances of a word in a
field
Hi guys,
I'm trying to find a MySQL function(s) that will allow me to count the
number of words in a field.
For eg:
[table: pages]
id - title - keywords - body
1 - Home - home, page - This is my home page. <p> Enjoy your stay.
2 - Feedback - feedback, form, contact - Please enter any feedback or
comments in the form below.<br>Your feedback will be used to improve our
service.
How do I, for example, count the number of instances of a word such as
"feedback" in say the BODY field?
I'm putting together a search engine & I'd like to 'rank' the results
based on the number of instances of each word found in a few fields.
In this example, if they search for the words "feedback" and "improve",
the count would result in the row id, word searched and the number of
instances of that word:
(formatting doesn't matter, it's just to give you an idea)
id 2: "feedback" count = 2
id 2: "improve" count = 1
I can play with the weightings later, just need to figure out the
counting...
Thanks guys,
Gav
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
