MySQL –– Fulltext indexing
written by: admin
Date Written: 4/16/09
Last Updated: 4/2/10
Fulltext indexing is done to make searches more intelligent, but primarily faster.
The basic format looks like
SELECT * FROM memoblog WHERE MATCH (summary) AGAINST ('words');
A CHAR, VARCHAR, or TEXT column must be indexed before the query can be successfully executed. The search term must be 4 or more characters long.
This cannot be altered on the godaddy servers unless I get a virtual server. There are some variables that can be changed via the
SET command, but changing
ft_min_word_len from 4 to 3 or lower is not available. To get around searches being ignored if they are found in 50% or more of the results we can use
SELECT * FROM memoblog WHERE MATCH (summary) AGAINST ('+version' IN BOOLEAN MODE);
however stopwords still apply.
Stopwords are those words that are very common, like "
the" "
that" "
also" or "
does"
ft_min_word_len changes can only be done at server startup. It appears that the only way to do this is with root access and cannot be altered on a shared server, which you get with economy hosting. In order to change these values you will need either a virtual or dedicated server. These give you root access to all of the system critical files. A dedicated server is much more expensive and is useful only if you have very high traffic needs.
Conclusion
With all of these restrictions I will not be pursuing FULLTEXT indexing at this time. It does not operate the way I want, so for now I will keep things the way they are. I could get it to work the way I want, but I would need to purchase a virtual dedicated server, which is too expensive for me and really not worth it just for this.
I need to be able to search for words like "CSS" an know that certain results are not being ignored if they are too common or are considered a 'stopword' or the word is too short or too long. Other than that it is much faster, but not worth it at this time.
Finding information on this issue is a bit difficult.
http://www.bigresource.com/MYSQL–ft_min_word_len–3–with–no–access–XACQh6Fr.html is helpful however.
TAGS: mysql