Delivering smooth and responsive web applications for users is essential in today’s fast paced digital world. Having covered optimizing Laravel performance for RestAPI with MySQL for enhancing the performance of web applications in our previous blog, we will now understand the database interactions, the core of many performance issues. As applications grow, ensuring efficient and responsive database interactions becomes more challenging and essential. In this comprehensive guide, we will focus exclusively on MySQL query optimization within Laravel RestAPI applications to understand the intricacies of SQL query design and leveraging Laravel’s built-in features for database performance. The case in point for this article is e-commerce data optimization.

Think of it like tuning a car’s engine for the best performance. We’ll look at a sample Ecommerce database (like checking out our car’s engine), see how it works, and then explore ways to make it run even better. By walking through real examples, you’ll see how little tweaks can make big differences. By the end, you’ll have a clearer idea of how to make databases work faster and smoother in Laravel RestAPI applications. Let’s get started!

Ecommerce Database Optimization

In the dynamic world of databases, it is often said that the architecture you choose can dictate the performance you achieve. For modern applications like e-commerce or social media apps, where data has grown exponentially and every millisecond of response time counts, a database’s design can be the difference between a seamless experience and a laggy interface. To illustrate this, let us see a practical example to witness the transformative power of effective database optimization.

A Glimpse into Our Sample Database

Our sample database supports a hypothetical e-commerce platform where customers can place orders for various products. At its core, the database consists of three pivotal tables- Orders, Customers, and Products, each of them serving a different function.

• Orders Table: This is the heart of our system, recording every transaction. Key data like order dates, shipment statuses, and quantities reside here.
• Customers Table: Here, all customer-related data is stored. From their first name to their contact details, this table offers a comprehensive view of the clientele.
• Products Table: This table is a catalog of all products available, detailing their name, category, price, and description.

While the structure may seem straightforward, the devil is in the details. The efficiency of the system is deeply intertwined with how each table is designed and indexed.

Data Type Conversion and Indexing Example

From the above sample database, we have three essential tables that form the foundation of an efficient customer order management system. The ‘Orders’ table records individual customer orders, including details such as order date, shipment status, and quantities.

To enhance database performance, a notable optimization has been introduced through the ‘Orders’ table. It not only streamlines data storage with optimized data types but also benefits from efficient indexing strategies. The use of binary values to represent order statuses reduces storage overhead, while carefully chosen indexes significantly improve query speed and responsiveness.

Additionally, the ‘Customers’ table stores customer details, and the ‘Products’ table contains product information. These tables are meticulously designed to support complex queries and analytics while ensuring the efficient management of customer orders and product data, contributing to a smooth and performant system.

Table & Structure

ORDERS

Structure:
CREATE TABLE Orders (
	OrderID INT PRIMARY KEY,
	CustomerID INT,
	ProductID INT,
	Quantity INT,
	OrderDate DATE,
	ShipDate DATE,
	Status VARCHAR(255)
);
Dummy Data:
INSERT INTO Orders (OrderID, CustomerID, ProductID, Quantity, OrderDate, ShipDate, Status)
SELECT
	n,
	100 + (n % 10),  -- Varying CustomerID from 100 to 109
	200 + (n % 5),   -- Varying ProductID from 200 to 204
	1 + (n % 10),	-- Varying Quantity from 1 to 10
	DATE_ADD('2023-01-01', INTERVAL (n % 365) DAY),  -- Varying OrderDate over a year
	DATE_ADD('2023-01-01', INTERVAL (n % 365) DAY),  -- Varying ShipDate over a year
	CASE WHEN n % 2 = 0 THEN 'Shipped' ELSE 'Delivered' END  -- Alternating Status
FROM (
	SELECT ROW_NUMBER() OVER() AS n
	FROM information_schema.tables
	LIMIT 1000  -- Number of rows to insert
) AS dummy_data;

CUSTOMERS

Structure:
CREATE TABLE Customers (
	CustomerID INT PRIMARY KEY,
	FirstName VARCHAR(50),
	LastName VARCHAR(50),
	Email VARCHAR(100),
	Address VARCHAR(255)
);
Dummy Data:
INSERT INTO Customers (CustomerID, FirstName, LastName, Email, Address)
SELECT
	n,
	CONCAT('First', n),
	CONCAT('Last', n),
	CONCAT('customer', n, '@example.com'),
	CONCAT(n, ' Random St, City, Country')
FROM (
	SELECT ROW_NUMBER() OVER() AS n
	FROM information_schema.tables
	LIMIT 1000  -- Number of rows to insert
) AS dummy_data;

PRODUCTS

Structure:
CREATE TABLE Products (
	ProductID INT PRIMARY KEY,
	ProductName VARCHAR(255),
	Category VARCHAR(50),
	Price DECIMAL(10, 2),
	Description TEXT
);
Dummy Data:
INSERT INTO `Products` (`ProductID`, `ProductName`, `Category`, `Price`, `Description`) VALUES
(200, 'Tool f', 'Tools', 29.99, 'Essential tool for various tasks.'),
(201, 'Widget A', 'Widgets', 19.99, 'High-quality widget with various features.'),
(202, 'Gadget B', 'Gadgets', 49.99, 'Innovative gadget with advanced technology.'),
(203, 'Tool C', 'Tools', 29.99, 'Essential tool for various tasks.'),
(204, 'Tool d', 'Tools', 29.99, 'Essential tool for various tasks.');

Case 1

In the first case, the “Status” column is defined with a data type of VARCHAR. This means that the status of an order, such as “Shipped” or “Delivered,” is stored as plain text. While this allows for human-readable status values, it can have performance implications. When querying the database for orders with a specific status, searching through text-based data can be slower compared to using a more efficient data type.

Query:

SELECT c.CustomerName, o.OrderID, p.ProductName,
	o.Quantity
FROM
	Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID
WHERE o.OrderDate >= '2023-02-01' AND o.OrderDate < '2023-03-01' AND o.Status = 1
ORDER BY o.OrderDate
DESC;

Result: (Showing rows 0 – 24 (9590 total, Query took 0.3187 seconds.))

Case 2

In the second case, the “Status” column’s data type has been changed to BIT. With BIT data type, status values are represented using binary values, typically 0 and 1. For example, 0 can represent “Delivered,” and 1 can represent “Shipped.” This change optimizes the database by reducing storage space and making it more efficient for querying. Searching for orders with a specific status now becomes a simple comparison operation, improving query performance.

Optimize Orders

ALTER TABLE `Orders` CHANGE `Status` `Status` BIT(1) NULL DEFAULT NULL;

Query:

SELECT c.CustomerName, o.OrderID, p.ProductName,
	o.Quantity
FROM
	Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID
WHERE o.OrderDate >= '2023-02-01' AND o.OrderDate < '2023-03-01' AND o.Status = 1
ORDER BY o.OrderDate
DESC;

Result: (Showing rows 0 – 24 (9590 total, Query took 0.1794 seconds.))

Case 3

In the third case, not only has the “Status” column’s data type been changed to BIT for improved storage efficiency, but additional indexes have also been added to further optimize performance. Indexes on columns such as “CustomerID,” “ProductID,” and “OrderDate” enhance query performance by enabling faster data retrieval. These indexes create a structured way to look up data, resulting in quicker responses to complex queries. This combination of data type optimization and the addition of indexes results in a highly performant database system, especially when dealing with large volumes of data and complex queries.

Query:

SELECT c.CustomerName, o.OrderID, p.ProductName,
	o.Quantity
FROM
	Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID
WHERE o.OrderDate >= '2023-02-01' AND o.OrderDate < '2023-03-01' AND o.Status = 1
ORDER BY o.OrderDate
DESC;

Index:

CREATE INDEX idx_customer_id ON Orders (CustomerID);
CREATE INDEX idx_product_id ON Orders (ProductID);
CREATE INDEX idx_order_date ON Orders (OrderDate);
CREATE INDEX idx_customer_order_date ON Orders (CustomerID, OrderDate);
CREATE INDEX idx_status ON Orders (Status);

Result: (Showing rows 0 – 24 (9590 total, Query took 0.0008 seconds.))

Final Thoughts

The power of database optimization becomes abundantly clear through these cases. As illustrated, making strategic changes to data types and investing in the right indexing strategies can produce dramatic improvements in performance, as evidenced by the drastic reduction in query execution times. This does not only translate to mere milliseconds saved. When scaled to real-world applications with thousands to millions of interactions daily, these optimizations can result in significant resource savings and enhance the user experience.

In our cases, a shift from a VARCHAR to a BIT datatype for the ‘Status’ column showcased the efficiency gains in storage and retrieval operations. The further introduction of indexes propelled performance enhancement, bringing down the query times considerably. It is a testament to the notion that, in database management, the nuanced, thoughtful decisions yield the most profound impacts.

As technology continues to advance and datasets grow larger, the value of effective database optimization cannot be overstated. For developers and database administrators, continuously revisiting, evaluating, and refining database structures and strategies becomes not just a technical exercise but a mandate to stay ahead in this digital age. Remember, a performant database is more than just a technical asset; it is a competitive advantage.

About the Author

Jeya Singh V is a Tech Lead at Siam Computing with over 8 years of experience in web development. His expertise lies in PHP, and he is particularly skilled in Laravel. He is passionate about crafting reliable and user-friendly solutions that leverage cutting-edge technologies. With a knack for problem-solving, he thrives on taking up and simplifying complex challenges. With the same passion, he is mentoring young developers and fostering a culture of growth within his team.