MySQL vs MongoDB Read Performance Analysis: Why Test Results Are Similar and Differences in Practical Applications

Test Background and Problem Description

During database selection, developers often have high expectations for MongoDB's performance. In a real test case, a user executed 1000 random reads of 15 records each from a MySQL table with 20 million records, indexed only on the id field. The test environment included a dual-core i7 CPU and 4GB RAM, with the MySQL table partitioned into 20 sections of 1 million records each.

The results showed that MongoDB was only about 1.1 times faster than MySQL, which disappointed the tester. The core question is: Why is the performance difference minimal when data organization and access methods are essentially the same?

Database Architecture and Performance Principles

MySQL, as a relational database, uses a tabular structure to store data, supporting complex joins and transactions. In the test case, data was stored in a single table and queried via an index on the id field. Due to similar index structures (B-tree) and single-table queries, MySQL efficiently utilized in-memory indexes, resulting in good performance.

MongoDB, as a document database, stores data in BSON format and supports nested structures. Under the same test conditions, MongoDB also queried via an index on the id field. Since the data model and query approach were nearly identical to MySQL, its performance advantages were not fully realized.

Real-World Scenarios for MongoDB Performance Advantages

MongoDB's true strength lies in its ability to organize data more sensibly for specific workloads. Consider storing a complex entity: in MySQL, this might be normalized into dozens of tables, each requiring indexes to maintain relational integrity. Querying the entity involves multiple index lookups and data retrievals, significantly increasing I/O operations.

In MongoDB, the entire entity can be stored as a single document in one collection. Querying requires:

• One index lookup (by id)
• Retrieval of one database page (the entire BSON document)

Thus, the I/O operations are logarithmic plus one (O(log n) + 1). If indexes reside entirely in memory, only one I/O is needed. In contrast, MySQL might require over 20 range lookups and data retrievals in the same scenario, often involving random I/O as data from different tables may be scattered across disk, further reducing performance.

Test Code Analysis and Optimization Suggestions

The PHP code used in the test has room for optimization. For example, creating a new database connection in each loop adds overhead. Below is an optimized version of the MongoDB test code:

<?php
function microtime_float() {
    list($usec, $sec) = explode(" ", microtime());
    return ((float)$usec + (float)$sec);
}

$time_taken = 0;
$tries = 100;

// Connect to database (moved outside loop)
$m = new Mongo();
$db = $m->swalif;

$time_start = microtime_float();
for ($i = 1; $i <= $tries; $i++) {
    $cursor = $db->posts->find(array('id' => array('$in' => get_15_random_numbers())));
    foreach ($cursor as $obj) {
        // Process data
    }
}
$time_end = microtime_float();
$time_taken = $time_end - $time_start;
echo $time_taken;

function get_15_random_numbers() {
    $numbers = array();
    for ($i = 1; $i <= 15; $i++) {
        $numbers[] = mt_rand(1, 20000000);
    }
    return $numbers;
}
?>

Similarly, MySQL test code should move database connections outside the loop to reduce connection overhead. Ensuring indexes are fully loaded into memory can further enhance query performance.

Database Selection in System Design

In system design, database choice should be based on actual application scenarios and data models. Platforms like Codemia offer extensive practice problems to help developers learn how to select appropriate data storage based on data relationships and access patterns.

For read-intensive tasks, if the data model is highly normalized and involves multiple table joins, MySQL may suffer from performance degradation due to multiple I/O operations. Conversely, if data can be naturally modeled as documents, MongoDB can significantly improve performance by reducing I/O counts.

Conclusion

The similar performance of MySQL and MongoDB in simple read tests does not imply equal performance in all scenarios. MongoDB's advantage lies in its flexible data model, which can enhance performance by reducing I/O operations in specific workloads. In practice, developers should choose the most suitable database based on data structure and access patterns, and optimize code and configurations to maximize potential.