Optimize Website Speed: A Beginner’s Guide to MySQL Indexing [ADB4]

Welcome to our beginner’s guide to MySQL indexing, where we unlock the secrets behind enhancing website performance. In this comprehensive tutorial, we’ll delve into the fundamentals of MySQL indexing, provide practical examples, and even set up a sample schema and data for hands-on testing. Let’s turbocharge your website’s speed together!

What is Table Indexing?

Imagine your database as a vast library of information. Table indexing acts as a well-organized catalog, allowing for quick retrieval of data by mapping column values to rows. It’s like finding a book in the library using the Dewey Decimal System.

Why is Table Indexing Important in MySQL?

MySQL indexing is essential for:

• Swift Data Retrieval: Indexes streamline the process of fetching data, especially from large tables.
• Improved Query Performance: By utilizing indexes, MySQL can filter and sort data efficiently, leading to faster query execution.
• Seamless Scalability: Proper indexing ensures optimal database performance, even as your website grows and data volumes increase.

Database performance and indexes

Database indexes in MySQL enable you to accelerate the performance of SELECT query statements. For small tables, an index does not help much. However, if you have tables with a large amount of data, indexes can dramatically improve performance.

The following behaviors are all indications that tables may benefit from adding indexes:

• Your site loads very slowly or not at all.
• An application is unable to connect to its database.
• Database queries are backed up.

If you experience any of these issues, you should analyze your database queries and consider adding indexes.

Setting Up the Environment:

Before diving into MySQL indexing, let’s set up our database environment. We’ll create a database, define a table, and populate it with sample data.

Creating the Database and Table:

First, let’s create a database named “ecommerce” with the UTF-8 encoding and the collation utf8mb4_general_ci, which supports storing data in the UTF-8 encoding, including emojis and special characters:

CREATE DATABASE ecommerce CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Next, let’s switch to the newly created database:

USE ecommerce;

Now, we’ll create a table named “products” to store information about different products:

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50)
);

Great! We now have our database and table ready for use.

Data Population: To make our examples more meaningful, let’s insert some sample data into the “products” table:

INSERT INTO products (id, name, category) VALUES
(1, 'Laptop', 'Electronics'),
(2, 'Smartphone', 'Electronics'),
(3, 'T-shirt', 'Apparel'),
(4, 'Headphones', 'Electronics'),
(5, 'Sneakers', 'Footwear');

Our database environment is now set up, and we have some sample data to work with. Let’s proceed to explore MySQL indexing.

How to Check if a Query is Using an Index?

Now that we have our schema and data set up, let’s experiment with the “EXPLAIN” statement in MySQL to analyze query execution plans and determine index usage.

For example, let’s analyze a query to retrieve all products in the “Electronics” category:

EXPLAIN SELECT * FROM products WHERE category = 'Electronics';

Output:

Explain of Query

Analyze the Output:

EXPLAIN output with all the possible types:

1. id: Represents the select query’s identifier within the EXPLAIN output. It indicates the sequence in which the SELECT statements are executed within the query. Possible value types:

• Numeric identifiers starting from 1, indicating the sequence of SELECT statements.

2. select_type: Describes the type of SELECT query. Possible value types:

• SIMPLE: Indicates a simple SELECT query without subqueries or UNIONs.
• PRIMARY: Indicates the outermost SELECT query in a UNION.
• SUBQUERY: Represents a subquery within the SELECT statement.
• DERIVED: Indicates a derived table resulting from subqueries in the FROM clause.
• UNION: Represents the result of a UNION operation.
• UNION RESULT: Indicates the result of a UNION.
• …

3. table: Specifies the table involved in the query execution. Possible value types:

• The name of the table being accessed in the query.

4. partitions: Indicates the partitions involved in the query. Possible value types:

• NULL: Indicates that partitions are not applicable or not used in the query.

5. type: Represents the access method used to retrieve rows from the table. Possible value types:

• ALL: Indicates a full table scan.
• index: Indicates a full index scan.
• range: Indicates that only a range of the index is scanned.
• ref: Indicates a reference to a single table row.
• eq_ref: Indicates a unique index lookup.
• const: Indicates a constant table (derived from the query).
• system: Indicates a query that reads from a table without examining the rows.
• …

6. possible_keys: Lists the indexes that the query could potentially use. Possible value types:

• Comma-separated list of index names.

7. key: Specifies the index actually used by the query. Possible value types:

• NULL: Indicates that no index is used.
• The name of the index being utilized.

8. key_len: Represents the length of the index that MySQL decides to use. Possible value types:

• Numeric value indicating the length of the index being used.

9. ref: Provides information on how the columns of the index are used to retrieve rows. Possible value types:

• NULL: Indicates no reference is used.
• Column name or constant value used for reference.

10. rows: Estimates the number of rows MySQL believes it must examine to execute the query. Possible value types:

• Numeric value indicating the estimated number of rows.

11. filtered: Indicates the percentage of rows filtered by the query condition. Possible value types:

• Numeric value representing the percentage.

12. Extra: Provides additional information about the query execution. Possible value types:

• Using where: Indicates that the WHERE clause is used for filtering.
• Using index: Indicates that the query uses only the index to retrieve data, without accessing the actual table.
• Using temporary: Indicates that a temporary table is created to hold intermediate results.
• Using filesort: Indicates that MySQL needs to do an extra sorting step.
• …

Adding, Removing, and Viewing Indexes in a Table:

To optimize query performance, we can create an index on the “category” column:

CREATE INDEX idx_category ON products (category);

ALTER TABLE products ADD INDEX idx_category2 (category);

And to view existing indexes:

SHOW INDEXES FROM products;

Output :

Here’s an explanation of each column in the JSON output from the “SHOW INDEXES FROM products” query:

1. Table: Specifies the name of the table for which the index information is displayed. In this case, it’s “products”.
2. Non_unique: Indicates whether the index allows duplicate values. A value of 0 means the index does not allow duplicates (unique index), while a value of 1 means it does allow duplicates.
3. Key_name: Represents the name of the index. For the first entry, it’s “PRIMARY”, indicating the primary key index. For the second entry, it’s “idx_category”, representing a secondary index on the “category” column.
4. Seq_in_index: Specifies the sequence number of the column within the index. For example, “1” indicates that it’s the first column in the index.
5. Column_name: Displays the name of the indexed column. In the first entry, it’s “id”, representing the primary key column. In the second entry, it’s “category”, indicating the column on which the secondary index is created.
6. Collation: Shows the collation used for the indexed column. In this case, it’s “A”, which typically means ASCII character set.
7. Cardinality: Refers to the number of unique values in the indexed column. It gives an estimate of the number of distinct values in the column.
8. Sub_part: Indicates the number of indexed characters if the column is only partially indexed. In this case, it’s null, meaning the entire column is indexed.
9. Packed: Specifies whether the index is packed. Null indicates that it’s not packed.
10. Null: Specifies whether the indexed column allows null values. “YES” indicates that null values are allowed, while an empty string (“”) implies that null values are not allowed.
11. Index_type: Represents the type of index used. In both entries, it’s “BTREE”, indicating a B-tree index.
12. Comment: Provides additional information about the index. In this case, it’s empty.
13. Index_comment: Displays any comments associated with the index. It’s empty in this example.
14. Visible: Indicates whether the index is visible. “YES” means the index is visible, while “NO” would mean it’s invisible. In this case, both indexes are visible.
15. Expression: Specifies an expression if the index is created on an expression rather than a column. Since it’s null in both entries, it indicates that no expression is used for indexing.

To remove the index:

DROP INDEX idx_category ON products;

Diagnosing Slow Website Performance Due to Indexing Issues: Slow website performance can often be traced back to indexing problems. By analyzing slow queries, evaluating existing indexes, optimizing queries, and monitoring website performance, you can diagnose and address these issues effectively.

Conclusion: By mastering MySQL indexing, you hold the key to unlocking lightning-fast website performance. Armed with knowledge, practical examples, and hands-on experience, you’re well-equipped to optimize your website and provide users with an exceptional browsing experience. Start your journey into MySQL indexing today and watch your website thrive!