Replace MySql Table with NoSQL DB

Backgroud:

I am not nosql fans. I don't have the habit to replace whole mysql db with nosql db. But if a mysql table can be replaced with nosql db and expensive queries are generated againest this table, I will give a try of nosql DB.

In our product, we have a unique user login log table to know how many users logined in a specify date. Here is the table structure:

  unique_login_logs: 
    _attributes:  {phpName: UniqueLoginLog}
    id:
    user_id: { type: integer }
    date: { type: date, index: true }
    created_at:
    _uniques:
      unique_date_type: [user_id, date]

The expensive query looks like:

select date, count(`user_id`) from unique_login_logs group by date order by date desc;'

And the other related operations will be save an entry into this table or check the user entry for a specify date is in the table or not. Nothing more.

Choose NoSQL DB from wiki page:

...
Key-value cache in RAM

    Citrusleaf database
    memcached
    Oracle Coherence
    Redis
    Tuple space
    Velocity

Key-value stores implementing the Paxos algorithm

    Keyspace

Key-value stores on disk

    BigTable
    CDB
    Citrusleaf database
    Dynomite
    Keyspace
    membase
    Memcachedb
    Redis
    Tokyo Cabinet
    TreapDB
    Tuple space
    MongoDB
...

For my feature request, I need a disk stored key-value system. I just spend 1 minute to take a look the MongoDB features. It meet my feature requirements. That's enough. If you know more about the other nosql DBs, of course you should choose the one which suit yours best.

Try MongoDB

Step 1: Install MongoDB(http://www.mongodb.org/display/DOCS/Quickstart+Unix);

Step 2: Simple demo testing:

create a new db:

use mydb;

Save an entry:

db.uniqueLoginLog.save({date: '2011-06-09', user_id: 101});
db.uniqueLoginLog.save({date: '2011-06-09', user_id: 111});
db.uniqueLoginLog.save({date: '2011-06-09', user_id: 102});
..
db.uniqueLoginLog.save({date: '2011-06-11', user_id: 100});
db.uniqueLoginLog.save({date: '2011-06-11', user_id: 101});
db.uniqueLoginLog.save({date: '2011-06-11', user_id: 102});

Group entries by date:

db.uniqueLoginLog.group(
           {key: { date:true },
            reduce: function(obj,prev) { prev.count ++; },
            initial: { count: 0 }
            });

The output looks like:

[
    {
        "date" : "2011-01-01",
        "count" : 3
    },
    {
        "date" : "2011-01-02",
        "count" : 3
    },
    {
        "date" : "2011-01-03",
        "count" : 1
    },
    {
        "date" : "2011-01-04",
        "count" : 1
    }
]

With the output, I can do the order in client.

Install PHP Mongo Extension

You can install Mongo PHP extension with pecl command line or install it manually

> pecl search mongo
Retrieving data...0%
Matched packages, channel pecl.php.net:
=======================================
Package Stable/(Latest) Local
mongo   1.1.4 (stable)        MongoDB database driver

> pecl install mongo

Get stored items with php function

PHP code:

$m = new Mongo();

$db = $m->mydb;

$collection = $db->uniqueLoginLog;


$obj = array('date'=> '2011-06-09', 'user_id'=> 101);
$collection->insert($obj);



$keys = array("date" => 1);
$initial = array("count" => 0);
$reduce = "function(obj,prev) { prev.count ++; }";
$g = $collection->group($keys, $initial, $reduce);

var_dump($g);

Output looks like:

array(4) {
  ["retval"]=>
  array(3) {
    [0]=>
    array(2) {
      ["date"]=>
      string(10) "2011-06-09"
      ["count"]=>
      float(17)
    }
    [1]=>
    array(2) {
      ["date"]=>
      string(10) "2011-06-11"
      ["count"]=>
      float(10)
    }
    [2]=>
    array(2) {
      ["date"]=>
      string(10) "2011-06-10"
      ["count"]=>
      float(7)
    }
  }
  ["count"]=>
  float(34)
  ["keys"]=>
  int(3)
  ["ok"]=>
  float(1)
}

Deploy to production?

Until now, the demo is still a toy. If you want to deploy it to production, there are more work need to to be done. Take a look at the admin zone

For my feature implemention, I will consider Replication next step.

Summary:

This is not a toturial about how to use MongoDB. It is just a example that can use nosql db in your production.

Reference: