Comparison Between MySQL and MongoDB: When to Effectively Use Each One and Application Examples

In the battle between MySQL and MongoDB, there’s no universal winner. Each is like a hero with unique powers, ready to save the day depending on the context. In this article, we’ll explore their differences, practical application examples, and answer key questions to help you choose the best tool.

What is each one used for?

MySQL: It is a relational database that uses tables with rows and columns. It is ideal for storing highly structured data, such as user registrations, financial transactions, or ERP systems.

MongoDB: It is a NoSQL database based on documents. It is ideal for storing unstructured or semi-structured data, such as logs, product catalogs, or social media content.

Examples:

• Use MySQL when you need strict relationships and referential integrity (e.g., an e-commerce system with order history and stock management).
• Use MongoDB when you need flexibility to dynamically alter the schema (e.g., applications storing heterogeneous data, like user profiles with custom fields).

What to store?

Application Examples

• MySQL:
  • Financial systems.
  • CRMs with rigid tables.
  • Applications requiring transactional integrity (ACID).
• MongoDB:
  • IoT applications storing sensor data.
  • Social networks handling posts in varied formats.
  • Log analysis applications.
• Using Both:
  • An e-commerce system might use MySQL for managing transactions and MongoDB for storing user comments, which have less rigid structures.

Scalability

• MongoDB: More horizontally scalable, using sharding to distribute data.
• MySQL: Limited vertical scalability, though replicas and partitions help.

Winner: MongoDB.

Security

• MySQL: Provides robust authentication and access control mechanisms, as well as encryption.
• MongoDB: Also offers strong security but historically suffered from insecure default configurations.

Winner: Technical tie, but MySQL has a slight edge due to its track record.

Learning Curve

• MySQL: Easier for those familiar with SQL and relational databases.
• MongoDB: Requires learning JSON-like structures and NoSQL concepts.

Winner: MySQL.

Availability of Workforce

MySQL has a larger base of trained professionals, though the MongoDB market is growing rapidly.

Winner: MySQL (for now).

Manageability

• MySQL: Mature tools like MySQL Workbench.
• MongoDB: Excellent tools like MongoDB Compass and Atlas, but relatively newer.

Winner: MySQL.

Debugging and Problem Resolution

• MySQL: Detailed error logs and debugging tools make troubleshooting faster.
• MongoDB: Flexible, but tracking errors in dynamic structures can be harder.

Winner: MySQL.

Average SLA and MTBF

• MySQL: Average SLA above 99.9999% with well-configured instances. High MTBF (Mean Time Between Failures) due to its maturity.
• MongoDB: Can also achieve a 99.99% SLA but is more prone to issues if poorly configured.

Practical Queries – Let’s Get to the Code, Shall We?

Data Model

• Products:
  • ID, Name, Category, Price, Stock.

In MySQL

-- Create the table
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  category VARCHAR(50),
  price DECIMAL(10, 2),
  stock INT
);

-- Insert data
INSERT INTO products (name, category, price, stock)
VALUES ('T-shirt', 'Clothing', 29.90, 100);

-- Queries
-- 1. Products in stock
SELECT * FROM products WHERE stock > 0;

-- 2. Products by category and price
SELECT * FROM products WHERE category = 'Clothing' AND price < 50;

In MongoDB

// Insert data
db.products.insertOne({
  name: "T-shirt",
  category: "Clothing",
  price: 29.90,
  stock: 100
});

// Queries
// 1. Products in stock
db.products.find({ stock: { $gt: 0 } });

// 2. Products by category and price
db.products.find({ category: "Clothing", price: { $lt: 50 } });

Complex Queries

Scenario: Sales System

Let’s use the tables customers, orders, and products as examples. We want to answer this question:

“Which customers placed orders with a total value above $1,000 in the last 6 months, also listing the products purchased?”

Table Structures

• customers: id_customer, name.
• orders: id_order, id_customer, order_date.
• products: id_product, product_name, price.
• order_product (relationship): id_order, id_product, quantity.

SQL Query

SELECT 
    c.name AS customer_name,
    SUM(op.quantity * p.price) AS total_value,
    GROUP_CONCAT(p.product_name) AS products_purchased
FROM 
    customers c
LEFT JOIN 
    orders o ON c.id_customer = o.id_customer
INNER JOIN 
    order_product op ON o.id_order = op.id_order
INNER JOIN 
    products p ON op.id_product = p.id_product
WHERE 
    o.order_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
GROUP BY 
    c.id_customer
HAVING 
    total_value > 1000
ORDER BY 
    total_value DESC;

Expected Result

    c.id_customer
HAVING 
    total_value > 1000
ORDER BY 
    total_value DESC;

Expected Result

Explanation

1. LEFT JOIN between customers and orders: Ensures that customers without orders still appear (if necessary).
2. INNER JOIN with order_product and products: Connects orders to purchased products.
3. WHERE: Filters orders placed in the last six months.
4. GROUP BY: Groups data by customer.
5. HAVING: Shows only customers with total order values above $1,000.
6. GROUP_CONCAT: Combines the names of purchased products into a single field.

In MongoDB

Data Examples

• Customers:

{
    "_id": ObjectId("1"),
    "name": "João da Silva"
}

• Orders:

{
    "_id": ObjectId("101"),
    "id_customer": ObjectId("1"),
    "order_date": ISODate("2024-06-15"),
    "items": [
        { "id_product": ObjectId("1001"), "quantity": 1 },
        { "id_product": ObjectId("1002"), "quantity": 2 }
    ]
}

• Products:

{
    "_id": ObjectId("1001"),
    "product_name": "Notebook",
    "price": 1500.00
}

MongoDB Query

db.orders.aggregate([
    {
        $match: {
            order_date: { $gte: new Date(new Date().setMonth(new Date().getMonth() - 6)) }
        }
    },
    {
        $lookup: {
            from: "customers",
            localField: "id_customer",
            foreignField: "_id",
            as: "customer"
        }
    },
    {
        $unwind: "$customer"
    },
    {
        $lookup: {
            from: "products",
            localField: "items.id_product",
            foreignField: "_id",
            as: "products"
        }
    },
    {
        $addFields: {
            total_value: {
                $sum: {
                    $map: {
                        input: "$items",
                        as: "item",
                        in: {
                            $multiply: [
                                "$$item.quantity",
                                { $arrayElemAt: ["$products.price", { $indexOfArray: ["$products._id", "$$item.id_product"] }] }
                            ]
                        }
                    }
                }
            }
        }
    },
    {
        $match: {
            total_value: { $gt: 1000 }
        }
    },
    {
        $project: {
            customer_name: "$customer.name",
            products_purchased: "$products.product_name",
            total_value: 1
        }
    }
]);

Explanation

1. $match: Filters orders placed in the last six months.
2. $lookup: Performs a “join” between orders and customers, and later between orders and products.
3. $unwind: Splits the resulting arrays to process each customer/product individually.
4. $addFields: Calculates the total value of the order by multiplying the quantity by the product price.
5. $project: Selects only the desired fields in the result.

Expected Result

[
    {
        "customer_name": "João da Silva",
        "total_value": 1500,
        "purchased_products": ["Notebook", "Mouse", "Keyboard"]
    },
    {
        "customer_name": "Maria Oliveira",
        "total_value": 1200,
        "purchased_products": ["Smartphone", "Bluetooth Headphones"]
    }
]

Which is better for complex queries?

• MySQL: More intuitive for those already familiar with SQL, but it may slow down with massive datasets.
• MongoDB: Flexible, but handling complex relationships can require more effort.

Recommendation: Choose based on your application type and the expertise of your team. Both tools can help you build powerful and efficient solutions!

Which to choose for building a new application?

• Use MySQL if you need consistency, structure, quick learning, and good availability of skilled professionals.
• Use MongoDB if you need flexibility and horizontal scalability.
• Use both when the problem is hybrid.

Whether you’re a Jedi or an Avenger, selecting the right database can turn your system into a true masterpiece.

Don’t forget to check out our database monitoring and observability SaaS, Flightdeck:

• Schedule a demo here.
• Learn more about Flightdeck here.