-
August 21st, 2008, 06:16 PM
#1
Faster String Query in Mysql
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:
Code:
$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?
-
August 22nd, 2008, 12:59 AM
#2
Re: Faster String Query in Mysql
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.
-
August 22nd, 2008, 02:40 PM
#3
Re: Faster String Query in Mysql
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?
-
August 24th, 2008, 09:22 AM
#4
Re: Faster String Query in Mysql
I suggest using MySQL Fulltext search to improve performance. This article can be a good intro:
Originally Posted by ajaxexpo
How about comparing strings in different formats?
Do you mean different character set?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|