# Using indexes to improve MySQL query performance

> Speed up slow MySQL queries on large tables by analysing them with EXPLAIN and adding, viewing or removing indexes.

Source: https://helpcenter.ruachost.com/developers/databases/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**

1. Log in via SSH:

```
mysql -u username -p database
```

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

## **More Information**

- [MySQL Optimization and Indexes](https://dev.mysql.com/doc/refman/8.4/en/optimization-indexes.html)
- [MySQL EXPLAIN Statement](https://dev.mysql.com/doc/refman/8.4/en/explain.html)