searching an array of strings in mysql

Question

i have an array of strings and i need to search whether the strings of the array exits in database or not. i am using following query:

foreach($array as $s)
{   
if(preg_match('/^(\bpho)\w+\b$/', $s))
{ 
    $query="select * from dictionary where words REGEXP '^".$s."$'";
    $result=$db->query($query) or die($db->error);
    if($result)
    {
    $count += $result->num_rows;
    }
}
}

but this query taking long time to execute. PLease provide a solution to reduce the searching time

if you can narrow your search to specific table or column then you can use index on that particular columns — Viswanath Polaki
– Viswanath Polaki, Commented Mar 12, 2014 at 7:11
Why are you using REGEXP when it looks like you're doing exact matches? Does $array contain regular expressions? — Barmar
– Barmar, Commented Mar 12, 2014 at 7:13
yes, u can see i am searching all the elements that starts with 'pho'. — user3409204
– user3409204, Commented Mar 12, 2014 at 7:20
Does $array contain things like pho\d*, i.e. are there regular expression operators in $array? Or are they just words like phonetic? — Barmar
– Barmar, Commented Mar 12, 2014 at 7:22
I understand why you're using preg_match, my question is why you're using WHERE words REGEXP. — Barmar
– Barmar, Commented Mar 12, 2014 at 7:22

Tynamo · Accepted Answer · 2014-03-12 07:12:46Z

1

I don't think your problem here is about your code. I think you should optimize your database. I'm not very good at it but I think you could add indexes in your database to speed up the research

answered Mar 12, 2014 at 7:12

Tynamo

14910 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Barmar Over a year ago

Indexes only have limited ability to optimize REGEXP matching.

Tynamo Over a year ago

and it's exactly what he need no?

Barmar Over a year ago

Maybe you should be more specific. What types of optimizations do you recommend to make the regular expression matching faster?

Barmar Over a year ago

You call that "more specific"?

Barmar · Accepted Answer · 2014-03-12 07:25:37Z

Combine all the search strings into a single regular expression using alternation.

$searches = array();
foreach ($array as $s) {
    if (preg_match('/^(\bpho)\w+\b$/', $s)) {
        $searches[] = "($s)";
    }
}
$regexp = '^(' . implode('|', $searches) . ')$';
$query="select 1 from dictionary where words REGEXP '$regexp'";
$result=$db->query($query) or die($db->error);
$count = $result->num_rows;

If $array doesn't contain regular expressions, you don't need to use the SQL REGEXP operator. You can use IN:

$searches = array();
foreach ($array as $s) {
    if (preg_match('/^(\bpho)\w+\b$/', $s)) {
        $searches[] = "'$s'";
    }
}
$in_list = implode(',', $searches);
$query="select 1 from dictionary where words IN ($in_list)";
$result=$db->query($query) or die($db->error);
$count = $result->num_rows;

Joey · Accepted Answer · 2014-03-12 07:19:11Z

0

Searching the whole database is a large job, I think a better way is you can cache some parts of the database, and than search in the cache. Redis is very good.

answered Mar 12, 2014 at 7:19

Joey

1,8014 gold badges15 silver badges21 bronze badges

Comments

devpro · Accepted Answer · 2016-01-10 16:49:06Z

0

Modify your query so that is doesn't select all table columns - that is a waste of resources. Instead, just let the database count the number of rows containing the search query and return back only a single answer (matches):

$query = "SELECT COUNT(id) AS matches FROM dictionary WHERE words REGEXP '^".$s."$'";

How are you indexing your database? If your words column is not indexed properly, then your regexp would take a long time. Examine your database structure and potentially add indexing to the words column.

P.S. And don't forget to fetch the matches column instead of using num_rows

edited Jan 10, 2016 at 16:49

devpro

16.1k3 gold badges29 silver badges39 bronze badges

answered Mar 12, 2014 at 7:26

Milen

1,5501 gold badge10 silver badges8 bronze badges

Collectives™ on Stack Overflow

searching an array of strings in mysql

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related