Mongodb and Symfony, Part 2: Simple Queries

Ok welcome to the second part of this matchup between MongoDB and MySQL 5.4 via Doctrine ORM on Symfony. In our previous test MongoDB blew away MySQL's MyISAM and InnoDB engines on raw insert speed. We were unable to use Doctrine for the inserts test as 2.5 million records quickly runs out of memory in Doctrine. So we opted for straight sql through PHP 5.3 commands with the MySQL native drivers. It wasn't even close.

So now let's see how MongoDB holds up against MySQL in the simple query arena.

Simple Queries

For our purposes here we'll only use the fastest setup and we'll vary the query parameters for different results. When I set up this test system I actually used a yaml schema so that we could test Doctrine. I had to abandon that in the insert tests, but here it should work fine.

Total Record Count

echo $mongo->selectDb('objects')
    ->selectCollection('users')->count();

echo Doctrine_Query::create()->from('User u')->count();

echo Doctrine_Query::create()->from('User u')->count();

Unindexed query

$query = array(
    'pet' => 'sugar glider',
);
echo $users->find($query)->count();

$users = Doctrine_Query::create()
    ->from('User u')
    ->where('u.pet = "sugar glider"');
echo $users->count();

$users = Doctrine_Query::create()
    ->from('User u')
    ->where('u.pet = "sugar glider"');
echo $users->count();

Indexed query

$query = array(
    'number' => 1005,
);
echo $users->find($query)->count();

$users = Doctrine_Query::create()
    ->from('User u')
    ->where('u.onumber = 1005');
echo $users->count();

$users = Doctrine_Query::create()
    ->from('User u')
    ->where('u.onumber = 1005');
echo $users->count();

Indexed range query

$query = array(
    'number' => array('$gt'=>1000,'$lt'=>1005),
);
echo $users->find($query)->count();

$users = Doctrine_Query::create()
    ->from('User u')
    ->where('u.onumber between 1001 and 1004');
echo $users->count();

$users = Doctrine_Query::create()
    ->from('User u')
    ->where('u.onumber between 1001 and 1004');
echo $users->count();

Indexed range query (large set)

$query = array(
    'number' => array('$gt'=>1000,'$lt'=>9005),
);
echo $users->find($query)->count();

$users = Doctrine_Query::create()
    ->from('User u')
    ->where('u.onumber between 1001 and 9004');
echo $users->count();

$users = Doctrine_Query::create()
    ->from('User u')
    ->where('u.onumber between 1001 and 9004');
echo $users->count();

Indexed range query, ordered, limit 10, with output

$query = array(
    'number' => array('$gt'=>1000,'$lt'=>9005),
);
$sort = array(
    'number' => -1,
);
foreach ($users->find($query)->sort($sort)->limit(10) as $user) {
echo $user['_id'],':',$user['number'], "\n";
}

$users = Doctrine_Query::create()
    ->from('User u')
    ->where('u.onumber between 1001 and 9004')
    ->orderBy('u.onumber desc')
    ->limit(10);
foreach ($users->execute() as $user) {
echo $user->getId(),':',$user->getOnumber(), "\n";
}

$users = Doctrine_Query::create()
    ->from('User u')
    ->where('u.onumber between 1001 and 9004')
    ->orderBy('u.onumber desc')
    ->limit(10);
foreach ($users->execute() as $user) {
echo $user->getId(),':',$user->getOnumber(), "\n";
}

Well we've got a lot of interesting numbers here. Seems as though MongoDB has a little trouble with large set range searches, and unindexed queries, but kicks butt in the other tests. And InnoDB seems to handle the large ranged queries the best with a modest showing in the rest, except for total record count.. Wth happened there? And MyISAM seems to be a pretty speedy running mate for everything, with an edge on the unindexed queries.

Real world value of these tests? I would have liked to run even more extensive queries here, but wanted to make sure I saved time for the next section which covers relations. But a decent real world example would be that last test. And MongoDB seems to have almost a 10ms edge on MySQL when it comes to this.

I realize that these results are not accounting for the overhead of using Doctrine. And I'm ok with that because I'm interested in real development environment stuff, not so much what is the bare bones potential. And so far MongoDB is doing well, although, the features of Doctrine would largely be missing from a MongoDB setup until we get a good framework that can sit on top of a MongoDB (which might even out the scores in many of these queries).

System Setup:

Some VPS setup with 1and1.com with a gig of dedicated ram
Ubuntu 8.04.1 64bit Hardy
Symfony 1.2.9 dev updated repository
MongoDB Linux 64 0.9.9 (mongodb-linux-x86_64-0.9.9.tgz)
Doctrine 1.0.10 (bundled with Symfony)
Apache 2.2.8
PHP 5.3.0 with mysqlnd
MySQL 5.4 with the my.cnf for medium systems
PECL MongoDB package 0.9.4