Releem Help Center

Privacy

main
/
blog
/
Mastering Index Creation in MySQL: Practical Examples, Workbench Tips, and Performance Optimization

Mastering Index Creation in MySQL: Practical Examples, Workbench Tips, and Performance Optimization

APR 01, 2025 • WRITTEN BY ROMAN AGABEKOV

Figuring out how to build efficient indexes for your MySQL is part technical know-how, part creative problem-solving. An indexing strategy is the key to success, regardless of whether you’re managing a small application or a high-traffic enterprise system. If you’re starting at sluggish queries and wondering where to start, we can help.

In this article, we’re going to explore MySQL index creation. We’re going to cover the fundamentals of index syntax with examples, take a look at MySQL workbench’s index capabilities, and discuss some advanced indexing techniques.

Table of contents

Why Focus on Indexing?
Covering the Fundamentals
Practical Examples
How to Use MySQL Workbench for Index Creation
Advanced Indexing Techniques
Performance Monitoring and Automatic Optimization with Releem

Why Focus on Indexing?

Indexes serve as optimized pointers that direct MySQL straight to the data rows you need. Without them, queries resort to exhaustive row-by-row scanning (the dreaded “full table scan”), a costly process as your tables grow larger. The right lookup structure cuts down on this inefficient scanning, directly improving query response times and database performance.

But how do you identify the best indexing candidates? Start by looking at your query habits closely. Columns frequently appearing WHERE, JOIN, or ORDER BY clauses are usually your first best bet. Queries involving exact-match lookups or sorting operations also scream for indexing.

Covering the Fundamentals

Let’s walk through some syntax basics and some quick tips with examples to help you get a grasp on the fundamentals.

CREATE INDEX Syntax

The CREATE INDEX statement is what you’ll use to build a pointer. It’s simple and lets you pick which columns to optimize. The basic structure is:

CREATE INDEX name_of_idx
ON table_name (selective_column1, selective_column2, ...);

For instance, when indexing the column for last names in an employee's table, you would execute:

CREATE INDEX idx_last_name ON employees (last_name);

If you need to prevent duplicate values, you can use CREATE UNIQUE INDEX instead.

Indexing at Table Creation vs. Adding Indexes to an Existing Table

Ideally, you can anticipate where you’ll need an index so you can embed it within the CREATE TABLE statement. For example:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    INDEX idx_employee_name (last_name, first_name)
);

However, if you did not create pointers when you created a table, you are not stuck. For an existing table, you can apply CREATE INDEX (as shown earlier) or use the ALTER TABLE command:

ALTER TABLE articles ADD FULLTEXT INDEX idx_content (title, content);

Quick Tips on Indexing Columns

Focus on WHERE clauses, JOIN, and ORDER BY operations to make these frequent tasks even faster.

Prioritize columns with diverse values. Low-cardinality columns with few distinct values offer less benefit because they have less discriminatory power to narrow down results.

Leverage composite pointers to filter on multiple columns. The most selective column should be listed first.

Limit indexes to columns that directly improve your workload. While indexing speed up reads, it can also slow down writes, so striking the right balance is key.

Practical Examples

Now, let’s go through some real-world examples of where and how you can apply these lookup fundamentals your database.

1. Indexing a Customer Table

A common query when managing an e-commerce database is to look up customers by their email for login or order tracking purposes. You can do this by indexing to skip the issue of a full table scan by targeting the email column:

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    email VARCHAR(75),
);

CREATE INDEX idx_email ON customers (email);

2. Order Filtering with a MySQL Composite Index for Better Performance

If you’re working with a table and want to narrow down results using a pair of fields, a multi-column pointer, also known as a composite index, can be your solution. For example, if you need to retrieve a shopper’s order using a unique id and the purchase date. A multi column index (composite index) significantly improves query performance...

CREATE TABLE purchases (
    purchase_id INT PRIMARY KEY AUTO_INCREMENT,
    shopper_id INT,
    purchase_date DATE,
    order_value DECIMAL(10, 2),
    fullfillment_status VARCHAR(20)
);

ALTER TABLE purchases 
ADD INDEX idx_shopper_verify (shopper_id, purchase_date);

3. Preventing Duplicate Entries with a Unique Index

In many cases, you’ll want to guarantee that no two users can register with the same username. By creating a unique index, you can enforce this restriction while simultaneously boosting lookup performance:

CREATE TABLE accounts (
    account_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(25),
    hashed_password VARCHAR(255),
    contact_email VARCHAR(75)
);

CREATE UNIQUE INDEX idx_username ON accounts (username);

How to Use MySQL Workbench for Pointer Creation

MySQL Workbench gives you a clean, efficient way to visualize the index creation process. It is especially useful for those who prefer a visual approach over command-line interactions. Here’s a walkthrough of how to optimize search retrieval with this tool:

1. Open up MySQL Workbench and link to your MySQL server. In the Navigator pane, double-click the database of interest with a double-click to reveal its full list of tables.

2. Right-click the table of interest and select either “Table Inspector” or “Alter Table” from the context menu. This opens a detailed view of the table’s framework.

3. In the Table Inspector window, you’ll see several tabs like “Columns” and “Foreign Keys.” Click on the “Indexes” tab.

Note: While Workbench provides a visual interface, you can also list all your keys using SQL commands. Check out our guide on how to inspect and show indexes in MySQL using the command line.

4. Click the last row in the index list (it’s a blank row). A new row will appear for you to input specifics. Give the index a meaningful name to keep things organized.

5. Under the “Column” area, use the dropdown menu to select the column(s) you want to index. You can select more than one column if you need a composite pointer.

6. By default, MySQL Workbench sets the index type to BTREE, which works well for most cases. If you need a different type, you can change it in the “Type” dropdown.

7. Once you’ve configured the index, hit “Apply” in the Table Inspector window. MySQL Workbench will generate the necessary SQL command and run it. Review the script, then click “Finish” to confirm.

Advanced Indexing Techniques

Indexing Virtual Views

Although MySQL does not allow for direct indexing on views, you can mimic this capability by leveraging materialized views or short-lived tables. For instance, if you are working on a complex query and want indexed access to its output, you might generate a temporary table to hold the query’s results and then attach a pointer to it. This provides the benefits of indexing while sidestepping MySQL’s limitations on views.

Multi-Valued Indexes for JSON Arrays

Starting with MySQL 8.0.17, MySQL introduced Multi-Valued Indexes — a special type of index designed for arrays stored inside JSON columns.

Instead of one index entry per row, it creates an index entry for each element in the JSON array. This makes searches inside arrays extremely fast.

Example:

ALTER TABLE products 
ADD INDEX idx_tags ((CAST(tags AS CHAR(100) ARRAY)));

JSON arrays with tags, categories, permissions, or product attributes when you frequently search for values inside them.

Prefix Indexing: Choosing the Right Index Length

For VARCHAR and TEXT columns, indexing the full value is often unnecessary and wasteful. A Prefix Index allows you to index only the first N characters, significantly reducing index size while maintaining good performance.

Example:

ALTER TABLE customers 
ADD INDEX idx_email (email(20));

Index Maintenance: Trade-offs with INSERT and UPDATE

Indexes speed up SELECT queries but slow down write operations. Every INSERT, UPDATE (of indexed columns), or DELETE forces MySQL to update all corresponding indexes.

Example:

INSERT INTO orders (...) VALUES (...);

An index is always a compromise between faster reads and slower writes. The more indexes you create, the faster your SELECT queries can become, but the slower your INSERT, UPDATE, and DELETE operations will be. Striking the right balance between read and write performance is one of the most important skills in database optimization.

Picking the Right Kind of Index

Starting with MySQL 8.0.17, MySQL introduced Multi-Valued Indexes — a special type of index designed for arrays stored inside JSON columns.

Instead of one index entry per row, it creates an index entry for each element in the JSON array. This makes searches inside arrays extremely fast.

Example:

CREATE TABLE quick_ref (ref_id INT, KEY idx_ref (ref_id) USING HASH) ENGINE=MEMORY;)

Full-text type is preferred when dealing with text, like product descriptions.

CREATE FULLTEXT INDEX idx_description ON items (description);

Spatial type is best for location-based data such as coordinates in a POINT or POLYGON column, allowing for fast spatial queries like distance calculations.

CREATE SPATIAL INDEX idx_geo ON locations (coordinates);

Performance Monitoring and Automatic Optimization with Releem

Overseeing a MySQL database can get tricky when sluggish or poorly crafted queries begin to sap performance. These queries chew up resources like CPU, memory, and disk I/O, bogging down your applications and taxing your server. Releem can make this easier with tools that monitor performance and optimize queries automatically.

Query Analytics

Releem’s Query Analytics feature gives you a live view into how your database queries are performing. Straight from the main dashboard, you get a rundown of your top 100 queries ranked by two critical metrics: average execution time and total load time:

Execution Time: Shows how long each query takes to complete, helping you spot the slow ones fast.

Total Load Time: Measures a query’s overall impact by factoring in how often it runs, with a color-coded bar to highlight the biggest resource hogs.

You can also sort the list by clicking “Avg. Execution Time” to view the slowest queries first or “Load on Total Time” to rank them by their impact on your resources.

Query Optimization

Even more value comes with Releem’s automatic query optimization feature that continuously assess index efficiency in real time and provide recommendations for adding, modifying, or removing indexes. These recommendations show up in the Query Optimization tab, ready to implement with minimal effort.

With Releem woven into your daily operations, your indexes stay finely tuned and your database runs like a well-oiled machine. It slashes the need for tedious manual adjustments, freeing you up to tackle the priorities that actually drive your work forward.

Taking the Next Steps

Think of this article as your roadmap into the world of MySQL index creation. Armed with the basics, you can build up to sophisticated techniques, eventually developing a robust indexing strategy that boosts query speed and supports your application’s growth.

As a next step, start by auditing your busiest tables – run an EXPLAIN on your most common queries to pinpoint where your efforts can make a difference. Then you can start applying indexes on high-cardinality columns for existing tables and you’ll see query times start to drop. If you’re not using Releem yet, give its Query Analytics feature a try. It is far easier and more efficient than relying on the manual grind of EXPLAIN. Plus, Releem can also help you optimize your most problematic queries with actionable indexing suggestions.

Indexing is less about perfection and more about momentum – so don’t be afraid to jump in there. Tweak, test, and before you know it, you’ll be running a tighter, faster system.

MySQL supports several index structures. By default, it uses B-Tree, but also supports Hash indexes. To learn the technical differences and how to evaluate them, read our guide on the different types of indexes in MySQL. If you need to optimize text search, MySQL offers a specialized structure. Check out our Comprehensive Guide to MySQL Full-Text Indexing.

Once your indexes are created, they may fragment over time. Learn how to rebuild and optimize indexes in MySQL.

Article by

Roman Agabekov

Founder & CEO

Roman Agabekov has 17 years of experience managing and optimizing MySQL and MariaDB in high-load environments. He founded Releem to automate routine database management tasks like performance monitoring, tuning, and query optimization. His articles share practical insights to help others maintain and improve their databases.

Keeping an Eye on MySQL with Health Checks
MySQL Performance Tuning Guide
How MySQL Configuration Impacts the Performance of Web Applications
How do you keep high-performance MySQL configuration?

Explore More

Get your own database advisor
Try Releem premium today for FREE!

Start Free Trial