Using indexes to improve MySQL query performance

Indexes in MySQL can significantly speed up SELECT queries, especially on large tables. For small tables, the impact may be minimal.

When to Consider Adding Indexes

Tables may benefit from indexes if you notice:

Slow site loading or unresponsive pages
Applications struggling to connect to the database
Queries being delayed or backed up

Note: On managed VPS or dedicated servers, increasing the sort buffer size may also help. Contact support if needed.

Analyzing Database Queries

Use the EXPLAIN SELECT statement to see how MySQL executes queries and identify performance bottlenecks.

Steps

mysql -u username -p database

At the mysql> prompt, run:

EXPLAIN SELECT * FROM table_name WHERE conditions \G

 Tip: For third-party apps like WordPress, examine the source code or logs to find the SELECT statements.

Example: Without Index

mysql> EXPLAIN SELECT title FROM employees WHERE lastname LIKE 'T%' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 142
        Extra: Using where

possible_keys and key are NULL, indicating no usable index.
MySQL will read all 142 rows, which is inefficient for large tables.

Example: With Index

After creating an index on lastname:

ALTER TABLE employees ADD INDEX index_name (lastname);
mysql> EXPLAIN SELECT title FROM employees WHERE lastname LIKE 'T%' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
         type: range
possible_keys: index_name
          key: index_name
      key_len: 22
          ref: NULL
         rows: 17
        Extra: Using where; Using index

possible_keys and key show MySQL will use the index.
Only 17 rows need to be read instead of 142.

Adding, Removing, and Viewing Indexes

Add an Index

ALTER TABLE table_name ADD INDEX index_name (table_column);

Remove an Index

ALTER TABLE table_name DROP INDEX index_name;

View Indexes

SHOW INDEX FROM table_name \G

Important:
Too many indexes can negatively impact performance because MySQL must maintain them. Balance is key.

Tip

After creating an index, run EXPLAIN SELECT again to check improvements. Monitor performance and adjust indexes as needed.