Searching Mysql

james2010

Is there any way to search for a particular word in a mysql text field containing many words?

dancahill

If you have mySQL 3.23.23 or later you can use the FULLTEXT search - basically create a FULLTEXT index for the column you want to search then in your PHP use MATCH $word AGAINST column name - see mySQL.com for details - this is the fastest way.

If you can't do this then use SELECT * FROM table name WHERE coumnname LIKE '%$variable%';

thom

The FULLTEXT thingy can be a bit fishy sometimes. You could also do something like this:

$searchString = sql_regcase($searchString);
$query = mysql_query("SELECT id FROM table WHERE field REGEXP '$searchString'");

dancahill

I'm thinking about upgrading my site to use FULLTEXT indexing on a later version of mySQL as I have been told that this is a faster & more reliable way of searching text fields than LIKE.

How do you mean fishy?? Do you think it is worth me making use of this feature? Is the REGEXP method you mention better than LIKE?

Appreciate any comments on this.

Tks,

Dan.

thom

Well, I did some experiments on FULLTEXT and the results were not like I would have wanted them to be. For instinct I had a hard time applying the index to varchar columns, even though it is documented that the index can be applied to varchar columns. I also couldn't find in the documentation how the match score was calculated ... seemed to me as a pretty random number with no roof or floor. It is a while since I was playing with those things so this feature might have been experimental at that time, so I might just be telling you the bollocks here 🙂

I think you should consider what your search is for when you choose how you're going to do your search. If you have a huge table you want to look up in, I would recommend using the FULLTEXT index. The difference between using REGEXP and LIKE is that one supports regular expressions and the other not. Come to think of it, REGEXP probably is a bit slower so using LIKE would probably be a more preferable choise.

-- Hope I didn't make things more complicated then they already were 🙂

chriskl

Don't forget that FULLTEXT is hundreds of times faster then regexp or like and that regexp and like are UNINDEXABLE.

Chris