MySQL Full Text Search - issues

[henryhenry]

Hello World :)Well I'm attempting to create a search function. I have made some lovely scripts that index my site so on my mysql DB I have a table with all the words in in individual rows (ie 1='php', 2='hello', ...) and another table with the references of where the words are on the site.My problem is the fulltext search - I'm mystified as to how it's meant to work. I have a word like 'help' with about 20 entries on various pages. I know it's there, I can see it! When I use LIKE, it comes up no problems. When I use MATCH() AGAINST() it doesn't find anything. I'm led to believe FULLTEXT search is quicker and more reliable but perhaps not in my case as I've done the indexing and I've also ranked words according to relevance. Maybe using like on an indexed column is quicker?Does mysql no like searching a table full of single words? Perhaps it is only for longer strings?Can anyone shed any light?Thanks in advance!!!Henry

[Yahweh]

Hello World :)Well I'm attempting to create a search function. I have made some lovely scripts that index my site so on my mysql DB I have a table with all the words in in individual rows (ie 1='php', 2='hello', ...) and another table with the references of where the words are on the site.My problem is the fulltext search - I'm mystified as to how it's meant to work. I have a word like 'help' with about 20 entries on various pages. I know it's there, I can see it! When I use LIKE, it comes up no problems. When I use MATCH() AGAINST() it doesn't find anything. I'm led to believe FULLTEXT search is quicker and more reliable but perhaps not in my case as I've done the indexing and I've also ranked words according to relevance. Maybe using like on an indexed column is quicker?Does mysql no like searching a table full of single words? Perhaps it is only for longer strings?Can anyone shed any light?Thanks in advance!!!Henry

See the FullText entry documentation. Fulltext search ignores words less than 4 letters long, it ignores this list of common words, and it ignores words that occur in 50% of the rows.I don't know what requirements you site has, but you don't actually need to create a seperate table for indexing words. You can put the fulltext index right on the table you're searching. For example, if you're table is set up like this:

[Articles]ID	Title		Article		...---	-----		-------1	   Bunnies		words2	Kitties		more words3	Learning PHP	some more4	Programming	yet more5	Blogs		words again6	MySQL Tutorial	once more

You can create your index like this:

ALTER TABLE articles ADD FULLTEXT(Title, Article)

To search your table, use this:

Select ID, Title, Article From Articles where Match(Title, Article) AGAINST ('words to search');

You don't need to build a seperate table with words to index, MySQL does it behind the scenes for you.

[henryhenry]

Hi Thanks for your reply. I'm going to have a think about what to do...My index works quite nicely although there are a few more limitations to work around. I have not found Fulltext search very effective for some reason. I think because I want to search a variety of data from across various tables. Maybe my site isn't structured so brilliantly! I'm still learning!