MongoDB vs MySQL: speed test part 2, Select queries

In the first part of this post I compared the performance of Insert operations for mongoDB and mysql. In this part I tried to compare the performance for different select operations. Test setup is same as used in the part 1.

1. Selects on an indexed column with different limit clauses

To check the performance of selects on an indexed column with limit clause, a number of queries with different limit clauses were executed on both the databases.

sample mysql query:
SELECT ID, NAME, BIRTH_DT, CONTACT_ADDRESS, CITY, TOTAL_EXP, ENTRY_DT, PROFILE, SUMMARY from db.resume where ID > 1000 limit 100000, 1000

sample MongoDB query:
$collection->find(array('ID' => array(':gt'=>1000)))->skip(100000)->limit(1000);

Start Limit	Total Records Fetched	MySQL	MongoDB
0	1000	0.846 ms	0.0710ms
100000	1000	0.903 ms	0.0391ms
200000	1000	0.969 ms	0.0209ms
300000	1000	1.029 ms	0.0889ms
400000	1000	1.058 ms	0.0488ms
500000	1000	1.149 ms	0.0482ms
600000	1000	1.214 ms	0.0460ms
700000	1000	1.170 ms	0.0469ms
800000	1000	1.196 ms	0.0450ms
900000	1000	1.216 ms	0.0460ms

2. Selects on a non indexed column with different limit clauses

sample mysql query:
SELECT ID, NAME, BIRTH_DT, CONTACT_ADDRESS, CITY, TOTAL_EXP, ENTRY_DT, PROFILE, SUMMARY from db.resume where TOTAL_EXP > 5 limit 100000, 1000

sample MongoDB query:
$collection->find(array('TOTAL_EXP' => array(':gt'=>5)))->skip(100000)->limit(1000);

Start Limit	Total Records Fetched	MySQL	MongoDB
0	1000	1.133 ms	0.0679 ms
100000	1000	1.166 ms	0.0469 ms
200000	1000	1.334 ms	0.0469 ms
300000	1000	1.293 ms	0.0438 ms
400000	1000	2.047 ms	0.0450 ms

3. Selects on an indexed column with sorting

sample mysql query:

SELECT ID, NAME, BIRTH_DT, CONTACT_ADDRESS, CITY, TOTAL_EXP, ENTRY_DT, PROFILE, SUMMARY from db.resume where ID > 1000 order by USERNAME asc

sample MongoDB query:

$collection->find(array('ID' => array(':gt'=>1000)))->sort(array("USERNAME"=>1));

Avg time in Mysql : 1.973 sec
Avg time in MongoDB : 0.138 ms

4. Selects with IN clause on an Indexed Column

To check the performance of select queries on an indexed key with IN clause, a number of queries were executed on both the databases and an avearge is taken. Each query had 100 random ID values in the IN clause.

Avg time in Mysql : 4.865 ms
Avg time in MongoDB : 1.570 ms

Its clear from the above results that MongoDB outperformed mysql in each case by a large margin.

MongoDB vs MySQL: speed test part 1, Insert queries

Recently I started exploring NoSQL databases as an alternative for some of our high traffic mysql tables. After going through a number of articles on net, I decided to explore MongoDB. I tried to compare the performance of different database operations (inserts/ different types of selects) in mongoDB and in MySQL. Performance comparison of insert operations are given here.

Test setup:
For testing I used a 3.16 GHz, Intel Xeon CPU with 2 GB of memory and 350 GB of disk.

MySQL:

key_buffer = 128M
sort_buffer_size = 512K
read_buffer_size = 256K
max_allowed_packet = 1M

Table schema:

ID int(11)
NAME varchar(35)
BIRTH_DT date
CONTACT_ADDRESS varchar(150)
CITY int(11)
TOTAL_EXP varchar(5)
ENTRY_DT date
PROFILE varchar(250)
SUMMARY varchar(250)

MongoDB:

For mongo two shard servers, one config server and one mongos were satarted on the same machine with chunk size set to 10.

Sample document:

{"_id" : ObjectId("4ca6cca6a87305c90b000000"),
"ID" : "5839427",
"NAME" : "Gaurav Asthana",
"BIRTH_DT" : "1981-06-29",
"CONTACT_ADDRESS" : "Noida, India",
"CITY" : "19",
"TOTAL_EXP" : "06.10",
"ENTRY_DT" : "2010-11-26",
"PROFILE" : "zxzzzzz zzzzzzzzzz zzzzzzzzzzzzzzzzzz zzzzzzzz",
"SUMMARY" : "abcfsf fsdfs gdgdfg gdfgdh dfghdh dfhdh" }

An index is also created on ID field.

I have created a simple php script to perform the benchmark. This script inserted a total of 15 Lac records both in the mysql and mongodb. I have recorded time for each batch of 100 records that were inserted. So, in total I recorded 15000 readings. The average time taken by both the databases is given below.

Average time per batch of 100 records :

Mysql : 18.77 ms
MongoDB : 5.53 ms

Size on disk:

Mysql : 390 MB
MongoDB : 1.6 GB

In my benchmark, MongoDB came out three times faster that mysql in case of insert queries. But it occupied four times more disk than mysql.