Well, like will do a full table scan if it starts with a wildcard, so you dont want to do it that way. I'd do full text search. http://dev.mysql.com/doc/mysql/en/MySQL_indexes.html -Jeff ----- Original Message ----- From: "Monty" <monty3@xxxxxxxxxxx> To: <php-db@xxxxxxxxxxxxx> Sent: Thursday, July 22, 2004 12:57 PM Subject: FULLTEXT and LIKE > 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 > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
