Stuck in the slow lane of database performance? Bottlenecks can be a relentless adversary. But fear not, as you’re about to unravel the mysteries of MySQL indexes.
In this action-packed tutorial, you’ll navigate the intricacies of MySQL indexes with practical, hands-on examples that will transform you into an indexing maestro.
Keep reading and master the art of optimizing database management!
Prerequisites
This tutorial comprises hands-on demonstrations. Ensure you have the following if you wish to follow along:
- A running Ubuntu 18.04 server or later – This tutorial uses an Ubuntu 20.04.
- MySQL is installed on your server with the necessary permissions for creating databases and tables.
Preparing a Sample Database
Before you unleash the full power of MySQL indexes, you’ll need a testing ground—a sample database where you can flex your SQL muscles. In this tutorial, you’ll create a database with sample data of company assets.
To create this sample database, follow the steps below:
1. Connect to your MySQL server.
2. Once connected, execute the following query to create a new database called company_assets (arbitrary). This database will store details on company assets like computers and office equipment.
CREATE DATABASE company_assets;
3. Next, run the following USE command to switch to the newly created database (company_assets). Doing so ensures that the following commands are executed in the correct database.
USE company_assets;
4. After switching, execute the following query to CREATE a TABLE named assets that holds simplified information about different assets in your company.
The AUTO_INCREMENT attribute for asset_id ensures a unique identifier is generated automatically for each new asset.
💡 Remember to end each SQL statement with a semicolon (
;) to denote its conclusion, which is vital in MySQL to avoid execution errors.
CREATE TABLE assets (
asset_id INT AUTO_INCREMENT PRIMARY KEY,
asset_name VARCHAR(100),
asset_type VARCHAR(50),
purchase_date DATE,
cost DECIMAL(10, 2)
);
5. Lastly, run the following query to insert data into your assets table.
INSERT INTO assets (asset_name, asset_type, purchase_date, cost) VALUES
('Laptop Pro 15', 'Electronics', '2022-03-15', 2399.99),
('Office Chair Deluxe', 'Furniture', '2022-04-22', 349.95),
('Conference Table', 'Furniture', '2022-05-30', 899.99),
('Projector Ultra HD', 'Electronics', '2022-06-11', 1459.00),
('Smartphone X', 'Electronics', '2022-07-08', 999.99),
('Printer All-in-One', 'Electronics', '2022-08-16', 489.99),
('Desk Wooden', 'Furniture', '2022-09-09', 559.95),
('Router High Speed', 'Electronics', '2022-10-04', 229.99),
('Webcam HD', 'Electronics', '2022-11-19', 129.99),
('Office Sofa', 'Furniture', '2022-12-21', 799.99);
Leveraging MySQL Single-Column Indexes to Improve Query Performance
With the sample data in place, you’re all set to delve into the world of MySQL indexes and optimize your queries for better performance. Imagine you have a book full of animal photos but only need ones with cats. Without an index, you’ll have to flip through every page, from the first to the last.
In the context of MySQL, the resolution to this dilemma is leveraging single-column indexes, like a page that tells you exactly where to find pictures of cats. As the name suggests, single-column indexes are created on a single column of a table.
To leverage single-column indexes for better query performance, carry out the following:
1. Execute the below SQL statements without an index, where the EXPLAIN command lets you get the execution plan of the query.
-- Retrieve all records from the 'assets' table where the cost is greater than 1000
SELECT * FROM assets WHERE cost > 1000;
-- Provide the query execution plan for the SELECT statement to understand how the database will execute the query
-- Used to analyze the performance of the query, such as which indexes are used and how rows are scanned
EXPLAIN SELECT * FROM assets WHERE cost > 1000;Notice the number of rows examined in the output. In this case, 10 rows are examined. MySQL reviews each row without an index, signifying a full table scan.
What if you have hundreds, if not thousands, of rows in the table? Can you imagine the load as you execute a query? Optimizing the query and adding appropriate indexes for better performance helps in such cases.
2. Next, run the following command to CREATE a single-column INDEX called idx_cost on the cost column within the assets table.
💡 Index names need to be unique within a table. If you try to create an index with the same name as an existing one, MySQL will give you an error.
CREATE INDEX idx_cost ON assets(cost);
3. With an index in place, rerun the following initial queries.
-- Retrieve all records from the 'assets' table where the cost is greater than 1000
SELECT * FROM assets WHERE cost > 1000;
-- Provide the query execution plan for the SELECT statement to understand how the database will execute the query
-- Used to analyze the performance of the query, such as which indexes are used and how rows are scanned
EXPLAIN SELECT * FROM assets WHERE cost > 1000;This time, you’ll notice a significant drop in examined rows, demonstrating the index’s efficiency.
Notice that only two rows are inspected in this example, translating to a potential 500% performance enhancement!
Ensuring Data Integrity with MySQL Unique Indexes
In any organizational setting, safeguarding the uniqueness of records in a company’s asset database is crucial. This assertion is akin to an art curator who meticulously avoids duplicates in their collection, ensuring each piece is unique.
Unique indexes are the tools that help maintain this level of integrity within your data assets, guaranteeing the exclusivity of each entry in a specified column.
To see how unique indexes work in ensuring data integrity, perform the following:
1. Run the below statement, without a unique index, to INSERT data INTO your assets table. With this command, you’ll end up with duplicate asset entries named Laptop Pro 15 in your assets table.
Without unique indexes, there’s a possibility of having multiple assets with the same name, leading to potential confusion and data inaccuracies.
INSERT INTO assets (asset_name, asset_type, purchase_date, cost) VALUES ( 'Laptop Pro 16', 'Laptop', '2020-05-21', 2300.00);
2. Next, execute the following query to list (SELECT) all (*) assets from the assets table.
SELECT * FROM assets;Notice the successful data insertion, including potential duplicates (Laptop Pro 15). Now that you’ve observed the effects of inserting data without a unique constraint, it’s time to enforce uniqueness to prevent future headaches.
Pick one asset_id (i.e., 13) of the duplicate asset to remove from the table.
3. Run the query below to DELETE the duplicate entry FROM your assets table, WHERE the asset_id value is 13.
DELETE FROM assets WHERE asset_id = 13;
4. Once deleted, execute the following command to CREATE a UNIQUE index called asset_name_index on the asset_name column.
CREATE UNIQUE INDEX asset_name_index ON assets(asset_name);
5. Lastly, run the following statement from step one to attempt to insert the duplicate data once more.
INSERT INTO assets (asset_name, asset_type, purchase_date, cost) VALUES ( 'Laptop Pro 16', 'Laptop', '2020-05-21', 2300.00);
This time, the unique index blocks the insertion, and MySQL returns an error, as shown below, indicating the unique constraint’s violation.
The unique index safeguards against duplicating entries in the asset_name column. Thus, this behavior preserves your asset database’s integrity and prevents the confusion and errors that can arise from duplicate records.
Listing and Removing MySQL Indexes
As your business evolves, so do your database requirements. There may come a time when certain indexes, once critical for performance and integrity, are no longer necessary.
In such cases, you can list all existing indexes to review their usage and, if needed, remove them to optimize your database performance.
To list and remove obsolete indexes, execute the following:
Run the command below to list (SHOW) each INDEX FROM the assets table in your database in comprehensive detail.
SHOW INDEX FROM assets;Note the name of the index (i.e., asset_name_index) you wish to delete.
Now, execute the following command to delete (DROP) your target INDEX (asset_name_index) on the assets table.
Note that this command should be used with caution, as removing an index can impact the performance of your database queries.
DROP INDEX asset_name_index on assets;
Conclusion
Throughout this tutorial, you’ve learned essential concepts that can significantly enhance your database performance, from single-column to unique indexes. You’ve covered vital aspects of leveraging MySQL indexes effectively, including optimizing them by listing and deleting indexes.
With these foundational skills, you can now confidently manage your database’s index structure to enhance overall performance and ensure the accuracy of your data assets.
As you continue enhancing your SQL skills, why not explore using multi-column indexes for more complex queries? Depending on your specific data needs, there are other handy types of indexes, such as spatial indexes.
Optimize data retrieval and management processes to support the growth and development of your business!
