Auto-suggest and MongoDB
Blogs20132013-01-09
Auto-suggest and MongoDB
Suppose we have a search form, we want to add auto-suggest feature in the search field. How to do that?
If using MySQL, there will be time-delay, the speed performance is not good enough. A easy improvement is to mix MongoDB and MySQL: save most visited keys in MongoDB.
Everytime when user key-in, check MongoDB first and return if found; if not found, then search in MySQL, and save a copy in MongoDB too.
This way is quite efficient. The following is the codes (PHP snippet, very quickly).
<?php
try {
// connect mongoDB server: localhost:27017
$m = new Mongo();
// select a database
$db = $m->auto_suggest;
}
catch ( MongoConnectionException $e ) {
die('Couldn't connect to mongodb, is the "mongoD" process running?');
}
$ary = array();
$collection = $db->search;
if(!empty($_GET['q'])) {
$q = trim($_GET['q']);
$regex = new MongoRegex("/$q/i");
$cursor = $collection->find(array('key'=> $regex));
$it = iterator_to_array($cursor);
if(! empty($it)) {
$count = 1;
foreach($cursor as $c) {
array_push($ary, iconv('UTF-8', 'UTF-8//TRANSLIT', $c{'key'}));
if( ++$count > 10) break;
}
}
if(!empty($ary)) {
echo json_encode($ary);
return;
}
else {
$mydb = mysql_pconnect(...) or die(mysql_error());
mysql_select_db('demo', $mydb);
mysql_query("SET NAMES 'utf8'", $mydb);
// use full-text and match()...against() to improve.
$query1 = "select keyword from keywords where keyword like '%" . $q . "%' order by keyword";
array_push_array($ary, mysql2mongo($collection, $query1, $mydb));
echo json_encode($ary);
}
}
function mysql2mongo($c, $sql, $mydb)
{
$a = array();
$res = mysql_query($sql, $mydb) or mysql_error();
if(mysql_num_rows($res)>0) {
while($row = mysql_fetch_array($res, MYSQL_NUM)) {
$t = iconv('UTF-8', 'UTF-8//TRANSLIT', $row[0]);
$a[] = $t;
$obj = array( 'key' => $t, 'count' => 1 );
$c->save($obj);
}
}
return $a;
}
function array_push_array(&$arr) {
$args = func_get_args();
array_shift($args);
if (!is_array($arr)) {
trigger_error(sprintf("%s: Cannot perform push on something
that isn't an array!", __FUNCTION__), E_USER_WARNING);
return false;
}
foreach($args as $v) {
if (is_array($v)) {
if (count($v) > 0) {
array_unshift($v, &$arr);
call_user_func_array('array_push', $v);
}
}
else $arr[] = $v;
}
return count($arr);
}
?>Some explains:
- This script is triggered via ajax/json, and return json data(hash/associated array)
- When user is typing in(key-up event), check the http request($_GET) value in both MongoDB and MySQL, according to the sequence.
MongoDB like cache, quicker than MySQL, so check first. If not found, then check MySQL. - if $_GET value exists in MongoDB’s title field, then push as much as 10 latest results into $ary array, and return this $ary(json format) immediately.
- if not found in MongoDB, then search in MySQL.
- if found in MySQL, save a copy in MongoDB, so next time this key will directly be fetched from MongoDB, thus quicker.
- if not found in MySQL, then discard it.
This way the search will be most efficient and high performance.