MySQL 4.0 Fulltext search

Now FindyPages.com has added the fulltext search features for searching pages. It creates a better search results on page keywords, page description, page name and page url.

However, as far as I can tell that MySQL 4.0 is having some restriction on the fulltext search.

Restrictions

A few restrictions affect MySQL

indices. Some of the default behaviors of these restrictions can be changed in your my.cnf or using the

command.

• 1

  FULLTEXT

  indices are NOT supported in InnoDB tables.

• MySQL requires that you have at least three rows of data in your result set before it will return any results.
• By default, if a search term appears in more than 50% of the rows then MySQL will not return any results.
• By default, your search query must be at least four characters long and may not exceed 254 characters.
• MySQL has a default

  1

  stopwords

  file that has a list of common words (i.e.,

  1

  the

  ,

  1

  that

  ,

  1

  has

  ) which are not returned in your search. In other words, searching for

  1

  the

  will return zero rows.

• According to MySQL’s manual, the argument to

  1

  AGAINST()

  must be a constant string. In other words, you cannot search for values returned within the query.

select count(members_pages.pageid) as record_count
from members
inner join members_pages on members.memberid = members_pages.memberid
where members.status_access = 1
and members_pages.status_access = 1
and members_pages.status_viewable_by = 1
and (
MATCH(members_pages.page_name, members_pages.page_keywords, members_pages.page_desc, members_pages.page_url) AGAINST (‘” . trim(strtolower(addslashes($keywords))) . “‘ IN BOOLEAN MODE)
or lower(members_pages.page_url) like ‘%” . trim(strtolower(addslashes($keywords))) . “%’)”;