▼  Site Navigation Main Articles News Search  ▼  Anime + Manga Anime Reviews Anime Characters Gallery Screenshots Manga Reviews  ▼  Misc Links to Webcomics Bible Quotes About Older Musings
site version 7.3
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.

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
copyright 2005–2024