How to optimize query with index mysql

MysqlHow-ToBeginner · 3 min read

How to Optimize Query Performance with Index in MySQL

To optimize a query in MySQL, create a INDEX on columns used in WHERE, JOIN, or ORDER BY clauses. Indexes help MySQL find rows faster by avoiding full table scans, improving query speed significantly.

📐

Syntax

Use the CREATE INDEX statement to add an index on one or more columns of a table. Indexes can be single-column or multi-column.

CREATE INDEX index_name ON table_name(column_name); creates an index on one column.
CREATE INDEX index_name ON table_name(column1, column2); creates a composite index on two columns.

Indexes speed up searches but add some overhead on data changes.

sql

CREATE INDEX index_name ON table_name(column_name);

💻

Example

This example shows how to create an index on the email column to speed up queries filtering by email.

sql

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100),
  email VARCHAR(100)
);

-- Insert sample data
INSERT INTO users (name, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'bob@example.com'),
('Carol', 'carol@example.com');

-- Create index on email
CREATE INDEX idx_email ON users(email);

-- Query using the index
EXPLAIN SELECT * FROM users WHERE email = 'bob@example.com';

Output

id: 1 select_type: SIMPLE table: users partitions: NULL type: ref possible_keys: idx_email key: idx_email key_len: 102 ref: const rows: 1 filtered: 100.00 Extra: Using index

⚠️

Common Pitfalls

Common mistakes when using indexes include:

Creating indexes on columns that are rarely used in queries, which wastes space and slows down inserts/updates.
Not using indexes on columns in WHERE or JOIN clauses, causing full table scans.
Using functions on indexed columns in queries, which can prevent index usage.
Creating too many indexes, which can slow down write operations.

sql

/* Wrong: Using function on indexed column disables index */
SELECT * FROM users WHERE LOWER(email) = 'bob@example.com';

/* Right: Use case-insensitive collation or store lowercase emails */
SELECT * FROM users WHERE email = 'bob@example.com';

📊

Quick Reference

Tips to optimize queries with indexes:

Create indexes on columns used in WHERE, JOIN, and ORDER BY.
Use EXPLAIN to check if queries use indexes.
Avoid functions on indexed columns in conditions.
Keep indexes lean to balance read and write performance.

✅

Key Takeaways

Create indexes on columns frequently used in filtering or joining to speed up queries.

Use EXPLAIN to verify if your query uses the index effectively.

Avoid applying functions on indexed columns in WHERE clauses to ensure index usage.

Too many indexes can slow down data modifications, so add only necessary indexes.

Indexes improve read speed but add overhead on writes; balance accordingly.