DEV Community

Cover image for Query Optimization and Indexing
arjun
arjun

Posted on

Query Optimization and Indexing

Day 8: Query Optimization and Indexing

Date: January 26, 2025

Today, we focus on Query Optimization and Indexing, essential skills for building efficient and high-performing database applications. As your database grows, the performance of your queries can significantly impact user experience. By learning how to optimize queries and use indexes effectively, you can ensure your database handles complex workloads efficiently.


Concepts

1. How Indexes Work and Why They Improve Performance

Indexes are data structures that databases use to quickly locate and retrieve data without scanning every row in a table. Think of an index like a book's table of contents: instead of flipping through every page to find a topic, you can jump directly to the right page.

Key Points:

  • Indexes are created on columns frequently used in queries, especially in WHERE, JOIN, and ORDER BY clauses.
  • They improve search speed but come at the cost of additional storage and slower write operations (e.g., INSERT, UPDATE, DELETE).

Types of Indexes:

  • Primary Index: Automatically created for the primary key.
  • Unique Index: Ensures all values in the column are unique.
  • Composite Index: Covers multiple columns to optimize queries using multiple conditions.

2. Query Plans (Using the EXPLAIN Command)

The EXPLAIN command in MySQL helps analyze how a query will be executed by showing its query plan. It highlights:

  • Whether indexes are being used.
  • Whether the query involves a full table scan (inefficient).
  • The order of operations the database performs.

How to Use EXPLAIN:

EXPLAIN SELECT * FROM users WHERE name = 'John';
Enter fullscreen mode Exit fullscreen mode

Key Terms in the Output:

  • type: Shows the type of join or scan (e.g., ALL, INDEX, ref).
    • ALL means a full table scan (inefficient).
    • ref or index indicates index usage (efficient).
  • key: The index used in the query.
  • rows: The number of rows MySQL estimates it will examine.

3. Avoiding Common Pitfalls

  • Full Table Scans: Occur when the database reads every row in a table to satisfy a query. Avoid them by using indexes.
  • Improper Indexing: Adding too many indexes can slow down write operations. Be strategic about which columns to index.
  • Wildcard Searches: Leading wildcards in LIKE queries (e.g., LIKE '%term') can’t use indexes effectively.

Practice

Step 1: Adding Indexes

Identify frequently queried columns in your project, especially those used in WHERE or JOIN clauses.

Example:

-- Create an index on the email column
CREATE INDEX idx_email ON users(email);
Enter fullscreen mode Exit fullscreen mode

Step 2: Compare Query Performance

Before adding an index, note the query execution time. Then, add the index and observe the difference.

Example Query:

-- Without index
SELECT * FROM users WHERE email = 'john@example.com';
Enter fullscreen mode Exit fullscreen mode

Use the EXPLAIN command to compare query plans before and after adding the index:

EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';
Enter fullscreen mode Exit fullscreen mode

Step 3: Optimizing Composite Queries

If your queries involve multiple columns, consider a composite index.

Example:

-- Create a composite index on first_name and last_name
CREATE INDEX idx_name ON users(first_name, last_name);

-- Query using the composite index
SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Doe';
Enter fullscreen mode Exit fullscreen mode

Real-World Example: Optimize Search Functionality

Let’s optimize the search functionality in your project, where users search for products by name or category.

Initial Query (Without Index):

SELECT * FROM products WHERE name LIKE '%phone%' OR category = 'Electronics';
Enter fullscreen mode Exit fullscreen mode

This query might result in a full table scan, especially if the products table has thousands of rows.

Adding Indexes:

-- Create indexes on frequently searched columns
CREATE INDEX idx_name ON products(name);
CREATE INDEX idx_category ON products(category);
Enter fullscreen mode Exit fullscreen mode

Optimized Query:

-- Faster execution with indexes
SELECT * FROM products WHERE name LIKE 'phone%' OR category = 'Electronics';
Enter fullscreen mode Exit fullscreen mode

Compare Performance:

Use EXPLAIN to verify the query plan and confirm index usage.


Additional Tips

  1. Avoid Over-Indexing:

    Each index takes up storage and slows down INSERT and UPDATE operations. Add indexes only where they are truly beneficial.

  2. Monitor Query Performance:

    Regularly analyze slow queries using tools like MySQL's slow query log or EXPLAIN.

  3. Rebuild Indexes:

    Over time, indexes can become fragmented. Use commands like OPTIMIZE TABLE to maintain performance.


Outcome for the Day

By the end of Day 8, you should:

  • Understand how indexes work and their impact on performance.
  • Use the EXPLAIN command to analyze and optimize queries.
  • Effectively apply indexing to improve search and query performance.

Next Step

In Day 9, we’ll explore Database Relationships and Normalization to design efficient and maintainable database schemas. Stay tuned!

Postgres on Neon - Get the Free Plan

No credit card required. The database you love, on a serverless platform designed to help you build faster.

Get Postgres on Neon

Top comments (0)

Billboard image

Try REST API Generation for Snowflake

DevOps for Private APIs. Automate the building, securing, and documenting of internal/private REST APIs with built-in enterprise security on bare-metal, VMs, or containers.

  • Auto-generated live APIs mapped from Snowflake database schema
  • Interactive Swagger API documentation
  • Scripting engine to customize your API
  • Built-in role-based access control

Learn more

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay