MySQL Index Challenges - Practical Coding Exercises

Create and Test a Composite Index

Add a composite index on the 'orders' table for columns 'customer_id' and 'order_date'. Then run a query filtering on these columns and analyze the EXPLAIN output.

Expected output: The EXPLAIN output should show the composite index being used.

Hint: Use CREATE INDEX and EXPLAIN statements.

Identify Unused Indexes

Analyze the 'products' table indexes and identify any that are not used by queries.

Expected output: A list of indexes that can be safely removed.

Hint: Use the performance_schema or slow query log to find unused indexes.

Optimize Query with Indexes

Given a slow query joining 'orders' and 'customers', create appropriate indexes to improve its performance.

Expected output: The query runs faster and uses indexes as shown by EXPLAIN.

Hint: Index the join columns and filter columns.

MySQL supports B-Tree, Hash, Full-text, and Spatial indexes, each suited for different data and query types.

A composite index covers multiple columns, allowing MySQL to efficiently filter or sort queries that use those columns together.

Excessive indexes slow down write operations like INSERT, UPDATE, and DELETE because each index must be updated.