MySQL vs MongoDB 1000 reads

by

MongoDB is not magically faster. If you store the same data, organised in basically the same fashion, and access it exactly the same way, then you really shouldn’t expect your results to be wildly different. After all, MySQL and MongoDB are both GPL, so if Mongo had some magically better IO code in it, then the MySQL team could just incorporate it into their codebase.

People are seeing real world MongoDB performance largely because MongoDB allows you to query in a different manner that is more sensible to your workload.

For example, consider a design that persisted a lot of information about a complicated entity in a normalised fashion. This could easily use dozens of tables in MySQL (or any relational db) to store the data in normal form, with many indexes needed to ensure relational integrity between tables.

Now consider the same design with a document store. If all of those related tables are subordinate to the main table (and they often are), then you might be able to model the data such that the entire entity is stored in a single document. In MongoDB you can store this as a single document, in a single collection. This is where MongoDB starts enabling superior performance.

In MongoDB, to retrieve the whole entity, you have to perform:

  • One index lookup on the collection (assuming the entity is fetched by id)
  • Retrieve the contents of one database page (the actual binary json document)

So a b-tree lookup, and a binary page read. Log(n) + 1 IOs. If the indexes can reside entirely in memory, then 1 IO.

In MySQL with 20 tables, you have to perform:

  • One index lookup on the root table (again, assuming the entity is fetched by id)
  • With a clustered index, we can assume that the values for the root row are in the index
  • 20+ range lookups (hopefully on an index) for the entity’s pk value
  • These probably aren’t clustered indexes, so the same 20+ data lookups once we figure out what the appropriate child rows are.

So the total for mysql, even assuming that all indexes are in memory (which is harder since there are 20 times more of them) is about 20 range lookups.

These range lookups are likely comprised of random IO — different tables will definitely reside in different spots on disk, and it’s possible that different rows in the same range in the same table for an entity might not be contiguous (depending on how the entity has been updated, etc).

So for this example, the final tally is about 20 times more IO with MySQL per logical access, compared to MongoDB.

This is how MongoDB can boost performance in some use cases.

More Related Contents:

  1. How can i write in NodeJS with MongoDB the following MySQL Select?
  2. MyISAM versus InnoDB [closed]
  3. How can i optimize MySQL’s ORDER BY RAND() function?
  4. Why does MYSQL higher LIMIT offset slow the query down?
  5. Is there a REAL performance difference between INT and VARCHAR primary keys?
  6. MySQL: Fastest way to count number of rows
  7. Why is MySQL InnoDB insert so slow?
  8. When to use MongoDB or other document oriented database systems? [closed]
  9. MySQL stored procedures use them or not to use them
  10. UUID performance in MySQL?
  11. MySQL “IN” operator performance on (large?) number of values
  12. How can I speed up a MySQL query with a large offset in the LIMIT clause?
  13. Optimal MySQL settings for queries that deliver large amounts of data?
  14. DOs and DONTs for Indexes [closed]
  15. MySql views performance [closed]
  16. MySQL performance: multiple tables vs. index on single table and partitions
  17. MySQL view performance
  18. “SELECT COUNT(*)” is slow, even with where clause
  19. Does Order of Fields of Multi-Column Index in MySQL Matter
  20. MySQL load data infile – acceleration?
  21. MySQL: Many tables or many databases?
  22. is php sort better than mysql “order by”?
  23. How can “set timestamp” be a slow query?
  24. Would it help to add index to BIGINT column in MySQL?
  25. ALTER TABLE ADD COLUMN takes a long time
  26. Why is connecting to MySQL server so slow?
  27. Mysql performance on 6 million row table
  28. Disadvantages of quoting integers in a Mysql query?
  29. Composite Primary Key performance drawback in MySQL
  30. Optimizing MySQL for ALTER TABLE of InnoDB

Leave a Comment