FULLTEXT and LIKE
| From: | Monty | Date: | Thu, 22 Jul 2004 18:57:16 +0000 |
| Subject: | FULLTEXT and LIKE | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-35435@lists.php.net to get a copy of this message | ||
Hi,
My head is swimming with lots of info gleaned from various newsgroups about
using fulltext indexes in MySQL, but, I'm still not sure what the best
solution would be for what I want to do. I'm concerned about the 3-char
minimum and not being able to search on partial words (my version of MySQL
doesn't support boolean fulltext searches).
I have a simple News table that is structured like this:
CREATE TABLE
news (
id int(7) unsigned NOT NULL auto_increment,
author varchar(50) NOT NULL default '',
headline varchar(60) NOT NULL default '',
content text NOT NULL,
url text NOT NULL,
UNIQUE KEY id (id),
) TYPE=MyISAM;
I want searches for News to be on the author, headline and content fields.
What is the best way to index and search this?
A) Create a combined fulltext index: FULLTEXT (author,headline,content) --
and do searches using MATCH() and AGAINST().
B) Create a fulltext index only for the content field and use this kind of
select: SELECT * FROM db WHERE author LIKE "%phrase%" OR headline LIKE
"%phrase%" OR MATCH(content) AGAINST(phrase);
C) None of the above ... is there a better way?
Thanks a lot for any input or feedback! I'm completely lost!
Monty