Mammatus Blog‎ > ‎

MongoDB versus MySQL and Oracle

posted Sep 23, 2012, 3:05 PM by Rick Hightower   [ updated Sep 27, 2012, 1:49 AM ]
MongoDB is document oriented but has many comparable concepts to traditional SQL/RDBMS solutions.

  1. Oracle: Schema, Tables, Rows, Columns
  2. MySQL: Database, Tables, Rows, Columns
  3. MongoDB: Database, Collections, Document, Fields
  4. MySQL/Oracle: Indexes
  5. MongoDB: Indexes
  6. MySQL/Oracle: Stored Procedures
  7. MongoDB: Stored JavaScript
  8. Oracle/MySQL: Database Schema
  9. MongoDB: Schema free!
  10. Oracle/MySQL: Foreign keys, and joins
  11. MongoDB: DBRefs, but mostly handled by client code
  12. Oracle/MySQL: Primary key
  13. MongoDB: ObjectID

If you have used MySQL or Oracle here is a good guide to similar processes in MongoDB:

Database Process TypeOracleMySQLMongoDB
Daemon/Serveroraclemysqldmongod
Console Clientsqlplusmysqlmongo
Backup utilitysqlplusmysqldumpmongodump
Import utilitysqlplusmysqlimportmongoimport

You can see a trend here. Where possible, MongoDB tries to follow the terminology of MySQL. They do this with console commands as well. If you are used to using MySQL, where possible, MongoDB tries to make the transition a bit less painful.

 

SQL operations versus MongoDB operations

MongoDB queries are similar in concept to SQL queries and use a lot of the same terminology. There is no special language or syntax to execute MongoDB queries; you simply assemble a JSON object. The MongoDB site has a complete set of example queries done in both SQL and MongoDB JSON docs to highlight the conceptual similarities. What follows is several small listings to compare MongoDB operations to SQL.

 

Insert

SQL
INSERT INTO CONTACTS (NAME, PHONE_NUMBER) VALUES('RICK HIGHTOWER','520-555-1212')
MongoDB
db.contacts.insert({name:'RICK HIGHTOWER',phoneNumber:'520-555-1212'})

Selects

SQL
SELECT name, phone_number FROM contacts WHERE age=30 ORDER BY name DESC
MongoDB
db.contacts.find({age:30}, {name:1,phoneNumber:1}).sort({name:-1})
SQL
SELECT name, phone_number FROM contacts WHERE age>30 ORDER BY name DESC
MongoDB
db.contacts.find({age:{$gt:33}}, {name:1,phoneNumber:1}).sort({name:-1})

Creating indexes

SQL
CREATE INDEX contact_name_idx ON contact(name DESC)
MongoDB
db.contacts.ensureIndex({name:-1})

Updates

SQL
UPDATE contacts SET phoneNumber='415-555-1212' WHERE name='Rick Hightower'
MongoDB
db.contacts.update({name:'Rick Hightower'}, {$set:{phoneNumber:1}}, false, true)

 

Additional features of note

MongoDB has many useful features like Geo Indexing (How close am I to X?), distributed file storage, capped collection (older documents auto-deleted), aggregation framework (like SQL projections for distributed nodes without the complexities of MapReduce for basic operations on distributed nodes), load sharing for reads via replication, auto sharding for scaling writes, high availability, and your choice of durability (journaling) and/or data safety (make sure a copy exists on other servers).

If you would like to learn more about MongoDB consider the following resources:

Comments