Faster String Query in Mysql

[ajaxexpo]

I'm using mysql and have a table with more than 150k rows with 8 columns. 3 of those columns are of type 'text'. I wanted to know what is the best method in searching for a particular string from this table.

Currently I'm searching by:


$sql = "SELECT * FROM $tbl_name ORDER BY id";
.
.
.
while($data = mysql_fetch_array($result))
.
.
if(preg_match('/$searchparam/i',$data['search']))


Using php to access the database, and using time(), it takes more than 5 seconds to return results. How can I optimize this?

[compavalanche]

Your not using the database to do the searching your streaming everything to your app then filtering.

Why not use mysql's like operator (e.g. where name like '%foo%' will find name values that contain foo)

You can also run regular expressions in mysql with REGEX operator. I would suggest you start out with the mysql docs try either the like or REGEX. Then profile the query with explain and see if you can add indexing to make it faster.

[ajaxexpo]

Thanks!

The 'like' operator works great!

How about comparing strings in different formats? I can't seem to find anything in the mysql documents about that.

The only thing I can think of is to create a new column in my table which has the hex value of the target column, then create an application function that converts the search string to 'hex' to 'the hex format of the new column.'

Would this be the best method?

[hspc]

I suggest using MySQL Fulltext search (http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html) to improve performance. This article (http://www.onlamp.com/pub/a/onlamp/2003/06/26/fulltext.html) can be a good intro:
How about comparing strings in different formats?
Do you mean different character set?