Birrel Birrel - 7 months ago 50
SQL Question

MySQL and PDO, speed up query and get result/output from MySQL function (routine)?

Getting the Value:

I've got the levenshtein_ratio function, from here, queued up in my MySQL database. I run it in the following way:

$stmt = $db->prepare("SELECT r_id, val FROM table WHERE levenshtein_ratio(:input, someval) > 70");
$stmt->execute(array('input' => $input));
$result = $stmt->fetchAll();

if(count($result)) {
foreach($result as $row) {
$out .= $row['r_id'] . ', ' . $row['val'];
}
}


And it works a treat, exactly as expected. But I was wondering, is there a nice way to also get the value that
levenshtein_ratio()
calculates?

I've tried:

$stmt = $db->prepare("SELECT levenshtein_ratio(:input, someval), r_id, val FROM table WHERE levenshtein_ratio(:input, someval) > 70");
$stmt->execute(array('input' => $input));
$result = $stmt->fetchAll();

if(count($result)) {
foreach($result as $row) {
$out .= $row['r_id'] . ', ' . $row['val'] . ', ' . $row[0];
}
}


and it does technically work (I get the percentage from the
$row[0]
), but the query is a bit ugly, and I can't use a proper key to get the value, like I can for the other two items.

Is there a way to somehow get a nice reference for it?

I tried:

$stmt = $db->prepare("SELECT r_id, val SET output=levenshtein_ratio(:input, someval) FROM table WHERE levenshtein_ratio(:input, someval) > 70");


modelling it after something I found online, but it didn't work, and ends up ruining the whole query.

Speeding It Up:

I'm running this query for an array of values:

foreach($parent as $input){
$stmt = ...
$stmt->execute...
$result = $stmt->fetchAll();

... etc
}


But it ends up being remarkably slow. Like 20s slow, for an array of only 14 inputs and a DB with about 350 rows, which is expected to be in the 10,000's soon. I know that putting queries inside loops is naughty business, but I'm not sure how else to get around it.

EDIT 1

When I use

$stmt = $db->prepare("SELECT r_id, val SET output=levenshtein_ratio(:input, someval) FROM table WHERE levenshtein_ratio(:input, someval) > 70");


surely that's costing twice the time as if I only calculated it once? Similar to having
$i < sizeof($arr);
in a for loop?

Answer

To clean up the column names you can use "as" to rename the column of the function. At the same time you can speed things up by using that column name in your where clause so the function is only executed once.

$stmt = $db->prepare("SELECT r_id, levenshtein_ratio(:input, someval) AS val FROM table HAVING val > 70");

If it is still too slow you might consider a c library like https://github.com/juanmirocks/Levenshtein-MySQL-UDF

doh - forgot to switch "where" to "having", as spencer7593 noted.

Comments