---
title: "How To Use MySQL Indexes Through Examples"
description: "Supercharge your database with MySQL indexes. Optimize queries, boost speed—master the art effortlessly, and elevate performance in this tutorial!"
canonical: "https://adamtheautomator.com/mysql-indexes/"
---

# How To Use MySQL Indexes Through Examples

> Supercharge your database with MySQL indexes. Optimize queries, boost speed—master the art effortlessly, and elevate performance in this tutorial!

Source: https://adamtheautomator.com/mysql-indexes/

---

ATA Learning

Tap to hide

[

ATA Learning

](/)

*   [Home](/)
*   [Tutorials](/tutorials/)
*   [Instructors](/author/)
*   [Advertising](/advertising/)
*   [Recommended Resources](/resources/)
*   [About Adam](/about-adam/)

Search for:  

*   [](https://twitter.com/adbertram)
*   [](https://github.com/Adam-the-Automator)
*   [](https://www.linkedin.com/company/adam-the-automator-llc)
*   [](/feed/)

![How To Use MySQL Indexes Through Examples](https://adamtheautomator.com/wp-content/uploads/2024/01/mysql-indexes.jpg)

# How To Use MySQL Indexes Through Examples

[![](https://secure.gravatar.com/avatar/2788bb1a3f735603f81eca51d68daec56a9d97e805a10268fb2c20afcc76b81b?s=192&d=mm&r=g)Nicholas Xuan Nguyen](https://adamtheautomator.com/author/nicholas-xuan-nguyen/)12 January 20246 min. read

Categories: [IT Ops](/category/it-ops/)

Tags:[Databases](/tag/databases/)[MySQL](/tag/mysql/)

Table of Contents

*   [Prerequisites](#prerequisites)
*   [Preparing a Sample Database](#preparing-a-sample-database)
*   [Leveraging MySQL Single-Column Indexes to Improve Query Performance](#leveraging-mysql-single-column-indexes-to-improve-query-performance)
*   [Ensuring Data Integrity with MySQL Unique Indexes](#ensuring-data-integrity-with-mysql-unique-indexes)
*   [Listing and Removing MySQL Indexes](#listing-and-removing-mysql-indexes)
*   [Conclusion](#conclusion)

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.

Related:[Install Ubuntu Server 20.04: A Step-by-Step Walkthrough](https://adamtheautomator.com/install-ubuntu/)

*   MySQL is installed on your server with the [necessary permissions](https://www.atlassian.com/data/admin/how-to-grant-all-privileges-on-a-database-in-mysql) for creating databases and tables.

Related:[How to Install MySQL on Ubuntu](https://adamtheautomator.com/install-mysql-on-ubuntu/)

## 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](https://dev.mysql.com/doc/mysql-getting-started/en/#mysql-getting-started-connecting).

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.

```sql
CREATE DATABASE company_assets;
```

![Creating a new sample database](https://adamtheautomator.com/wp-content/uploads/2024/01/image-71.png)

Creating a new sample database

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.

```sql
USE company_assets;
```

![Switching to the newly created database](https://adamtheautomator.com/wp-content/uploads/2024/01/image-70.png)

Switching to the newly created database

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._

```sql
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)
);
```

![Creating a table to hold information about different assets](https://adamtheautomator.com/wp-content/uploads/2024/01/image-69.png)

Creating a table to hold information about different assets

5\. Lastly, run the following query to insert data into your `assets` table.

```sql
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);
```

![Inserting data into the table](https://adamtheautomator.com/wp-content/uploads/2024/01/image-68.png)

Inserting data into the table

## 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](https://adamtheautomator.com/mysql-full-text-searching/) 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.

```sql
-- 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;
```

Related:[Accurate Database Searches with MySQL Full Text Searching](https://adamtheautomator.com/mysql-full-text-searching/)

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.

![Running SQL statements without an index](https://adamtheautomator.com/wp-content/uploads/2024/01/image-74.png)

Running SQL statements without an index

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._

```sql
CREATE INDEX idx_cost ON assets(cost);
```

![Creating an index on the cost column](https://adamtheautomator.com/wp-content/uploads/2024/01/image-73.png)

Creating an index on the cost column

3\. With an index in place, rerun the following initial queries.

```sql
-- 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!

![mysql indexes - Running SQL statements with an index](https://adamtheautomator.com/wp-content/uploads/2024/01/image-72.png)

Running SQL statements with an index

## 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.

```sql
INSERT INTO assets (asset_name, asset_type, purchase_date, cost) VALUES ( 'Laptop Pro 16', 'Laptop', '2020-05-21', 2300.00);
```

![Inserting data without a unique index](https://adamtheautomator.com/wp-content/uploads/2024/01/image-79.png)

Inserting data without a unique index

2\. Next, execute the following query to list (`SELECT`) all (`*`) assets from the `assets` table.

```sql
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.

![Listing all assets from the assets table](https://adamtheautomator.com/wp-content/uploads/2024/01/image-78.png)

Listing all assets from the assets table

3\. Run the query below to `DELETE` the duplicate entry `FROM` your `assets` table, `WHERE` the `asset_id` value is `13`.

```sql
DELETE FROM assets WHERE asset_id = 13;
```

![Deleting the duplicate entry](https://adamtheautomator.com/wp-content/uploads/2024/01/image-77.png)

Deleting the duplicate entry

4\. Once deleted, execute the following command to `CREATE` a `UNIQUE` index called `asset_name_index` on the `asset_name` column.

```sql
CREATE UNIQUE INDEX asset_name_index ON assets(asset_name);
```

![Creating a unique index](https://adamtheautomator.com/wp-content/uploads/2024/01/image-76.png)

Creating a unique index

5\. Lastly, run the following statement from step one to attempt to insert the duplicate data once more.

```sql
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.

![Attempting to insert an asset with a unique index](https://adamtheautomator.com/wp-content/uploads/2024/01/image-75.png)

Attempting to insert an asset with a unique index

## 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.

```sql
SHOW INDEX FROM assets;
```

Note the name of the index (i.e., **asset\_name\_index**) you wish to delete.

![Listing all indexes from the assets table](https://adamtheautomator.com/wp-content/uploads/2024/01/image-80.png)

Listing all indexes from the assets table

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.

```sql
DROP INDEX asset_name_index on assets;
```

![Deleting an index](https://adamtheautomator.com/wp-content/uploads/2024/01/image-81.png)

Deleting an index

## 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](https://dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html) for more complex queries? Depending on your specific data needs, there are other handy types of indexes, such as [spatial indexes](https://dev.mysql.com/doc/refman/8.0/en/creating-spatial-indexes.html).

Optimize data retrieval and management processes to support the growth and development of your business!

Share this article

[Share on X](https://twitter.com/intent/tweet?url=https%3A%2F%2Fadamtheautomator.com%2Fmysql-indexes%2F&text=How%20To%20Use%20MySQL%20Indexes%20Through%20Examples)[Share on Facebook](https://www.facebook.com/sharer/sharer.php?u=https%3A%2F%2Fadamtheautomator.com%2Fmysql-indexes%2F)[Share on LinkedIn](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fadamtheautomator.com%2Fmysql-indexes%2F)

## Related Posts

![](https://adamtheautomator.com/wp-content/uploads/2022/06/How-To-Back-Up-Restore-Databases-via-the-Mysqldump-Command.jpg)

### [How To Back-Up & Restore Databases via the Mysqldump Command](/mysqldump-command/)

Learn how to use the mysqldump command to back-up and restore databases in MySQL to save the day and make sure you don’t lose any data!

![](https://adamtheautomator.com/wp-content/uploads/2022/04/How-to-Install-MySQL-on-Ubuntu.jpg)

### [Install MySQL on Ubuntu: Get Your Database Server Up and Running](/install-mysql-on-ubuntu/)

Want a robust, secure database for your projects? Discover how to install MySQL on Ubuntu and implement essential security measures with this guide.

![](https://adamtheautomator.com/wp-content/uploads/2023/12/mysql-stored-procedures.jpg)

### [Advanced Database Techniques with MySQL Stored Procedures](/mysql-stored-procedures/)

Learn how to use MySQL Stored Procedures effectively to make database operations more efficient in this ATA Learning tutorial!

## Categories

*   [IT Ops](/category/it-ops/)
*   [Cloud](/category/cloud/)
*   [DevOps](/category/devops/)
*   [Home Ops](/category/home-ops/)
*   [Information Security](/category/infosec/)
*   [Software Development](/category/software-development/)

## Site

*   [Home](/)
*   [Tutorials](/tutorials/)
*   [Instructors](/author/)
*   [Advertising](/advertising/)
*   [Recommended Resources](/resources/)
*   [About Adam](/about-adam/)

Copyright 2026© ATA Learning | [Privacy Policy](/privacy/)