William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

Auto-suggest and MongoDB

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).

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);
  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 );
  return $a;
function array_push_array(&$arr) {
  $args = func_get_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:

  1. This script is triggered via ajax/json, and return json data(hash/associated array)
  2. 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.
  3. 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.
  4. if not found in MongoDB, then search in MySQL.
  5. if found in MySQL, save a copy in MongoDB, so next time this key will directly be fetched from MongoDB, thus quicker.
  6. if not found in MySQL, then discard it.
    This way the search will be most efficient and high performance.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: