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 FULLTEXT indices. Some of the default behaviors of these restrictions can be changed in your my.cnf or using the SET command.

  • 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 stopwords file that has a list of common words (i.e., the, that, has) which are not returned in your search. In other words, searching for the will return zero rows.
  • According to MySQL's manual, the argument to 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))) . "%')";
Post a Comment