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.