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

What is each one for?

• MySQL: A relational database that uses tables with rows and columns. It is ideal for storing highly structured data, such as records, financial transactions, or ERP systems.
• MongoDB: A NoSQL document-based database. 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 rigid relationships and referential integrity (e.g., e-commerce with order history and inventory).
• Use MongoDB when you need flexibility to dynamically alter the schema (e.g., applications storing heterogeneous data, such as 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 media handling posts in varied formats.
  • Log analysis applications.
• Using Both:
  • An e-commerce platform can use MySQL to manage transactions and MongoDB to store 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: Offers robust authentication and access control mechanisms, along with encryption.
• MongoDB: Also has strong security, but historically suffered from insecure default configurations.

Winner: Technical tie, but MySQL has an edge due to its history.

Learning Curve

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

Winner: MySQL.

Workforce Availability

MySQL has a larger base of trained professionals, but 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, though relatively new.

Winner: MySQL.

Debugging and Troubleshooting

• MySQL: Detailed error logs and debugging tools help resolve issues quickly.
• MongoDB: Flexible but can make tracking errors harder in dynamic structures.

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 susceptible to issues if poorly configured.

Practical Queries – Let’s Dive into the Code, thats what you wanna see right?

Data Model

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

In MySQL


-- Create the table
CREATE TABLE produtos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(100),
  categoria VARCHAR(50),
  preco DECIMAL(10, 2),
  estoque INT
);

-- Insert data
INSERT INTO produtos (nome, categoria, preco, estoque)
VALUES ('T-Shirt', 'Clothing', 29.90, 100);

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

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

In MongoDB

// Insert data
db.produtos.insertOne({
  nome: "T-Shirt",
  categoria: "Clothing",
  preco: 29.90,
  estoque: 100
});

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

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

Complex Queries

Scenario: Sales System

We will use the tables customers, orders, and products as an example. The goal is to answer the following question:

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

Table Structure:

• customers: customer_id, name.
• orders: order_id, customer_id, order_date.
• products: product_id, product_name, price.
• order_product (relationship): order_id, product_id, quantity.

SQL Query

SELECT 
    c.name AS customer_name,
    SUM(order_prod.quantity * prod.price) AS total_value,
    GROUP_CONCAT(prod.product_name) AS purchased_products
FROM 
    customers c
LEFT JOIN 
    orders o ON c.customer_id = o.customer_id
INNER JOIN 
    order_product order_prod ON o.order_id = order_prod.order_id
INNER JOIN 
    products prod ON order_prod.product_id = prod.product_id
WHERE 
    o.order_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
GROUP BY 
    c.customer_id
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 the purchased products.
3. WHERE: Filters orders placed in the last 6 months.
4. GROUP BY: Groups the data by customer.
5. HAVING: Displays only customers with a total order value above $1,000.00.
6. GROUP_CONCAT: Combines the names of purchased products into a single field.

MongoDB

// Clientes:
{
    "_id": ObjectId("1"),
    "nome": "João da Silva"
}
// Pedidos:
{
    "_id": ObjectId("101"),
    "id_cliente": ObjectId("1"),
    "data_pedido": ISODate("2024-06-15"),
    "itens": [
        { "id_produto": ObjectId("1001"), "quantidade": 1 },
        { "id_produto": ObjectId("1002"), "quantidade": 2 }
    ]
}

// Produtos:

{
    "_id": ObjectId("1001"),
    "nome_produto": "Notebook",
    "preco": 1500.00
}

MongoDB Query

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

Explanation:

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

Expected Results

[
    {
        "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 familiar with SQL, but it can become slow with massive datasets.
• MongoDB: Flexible but requires more effort to handle complex relationships.

Choose based on the type of application and the team available. With both, you can build powerful and efficient solutions!

Which Should You Choose for Building a New Application?

• Use MySQL if you need consistency and 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, choosing 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!