Task 1: Compare Query Performance With and Without Index
Objective: Measure the performance difference between indexed and non-indexed columns.
• Create a table with at least 1 million records.
• Run a SELECT query using a non-indexed column.
• Now create an index on that column.
• Rerun the SELECT query and compare execution time using EXPLAIN.
Task 2: Create and Use Composite Index
Objective: Understand the impact of column order in composite indexes.
• Create a table with at least three columns: first_name, last_name, email.
• Add a composite index on (last_name, first_name).
• Execute queries filtering by:
o Only last_name
o Only first_name
o Both
• Observe EXPLAIN plan to understand index usage.
Task 3: Fulltext Indexing on Large Text Column
Objective: Explore fulltext indexing for efficient search on large text fields.
• Create a table with a description column of type TEXT.
• Add a fulltext index.
• Run a MATCH ... AGAINST query and evaluate performance.
• Compare to a LIKE '%keyword%' search.
Task 4: Analyze Covering Indexes
Objective: Use covering indexes to satisfy queries without accessing the table.
• Create a table with columns: id, title, author.
• Create an index on (title, author).
• Run SELECT title, author WHERE title='Some Title'.
• Use EXPLAIN to confirm if it’s using a covering index.
Task 5: Monitor Index Usage and Identify Unused Indexes
Objective: Learn to monitor index usage and clean up unnecessary indexes.
• Use performance_schema.table_io_waits_summary_by_index_usage to find unused
indexes.
• DROP those indexes and observe the impact.
Task 6: Evaluate Indexes with ORDER BY Queries
Objective: Understand how indexes can (or can't) optimize ORDER BY queries.
• Create an index on the column being ordered.
• Use EXPLAIN to check if Using index appears.
• Change the order direction or add LIMIT to test performance differences.
Task 7: Indexing JSON and Virtual Columns
Objective: Use generated virtual columns to index JSON data.
• Create a table with a json_data JSON column.
• Create a virtual column for one JSON attribute (e.g., json_data->>'$.user_id').
• Index the virtual column.
• Run queries using this column.
Task 8: Partial Indexes on Prefix of Text Fields
Objective: Save space and still gain performance benefit by indexing prefixes.
• Create a table with email VARCHAR(255).
• Index the first 20 characters of the column.
• Evaluate how well this index performs using queries.
Task 9: Use of BTREE vs HASH Indexes (on MEMORY Table)
Objective: Compare BTREE and HASH performance for different query types.
• Create a MEMORY table.
• Create two indexes: one BTREE and one HASH.
• Compare performance for equality vs. range queries.
Task 10: Dropping and Rebuilding Indexes on Large Table
Objective: Learn the effect of dropping and recreating indexes during bulk insert.
• Create a table with indexes.
• Insert large dataset with and without indexes.
• Measure time.
• Drop and rebuild indexes after insert.
• Compare overall performance.
Assignment Questions (To Solve After Lab)
1. What is the difference between a primary index and a secondary index in MySQL?
2. How does a covering index improve performance?
3. Can you index a TEXT or BLOB field in MySQL? If so, how?
4. What is the effect of indexing a low cardinality column?
5. Explain why LIKE '%keyword%' cannot use a standard index.
6. What are the trade-offs of using composite indexes?
7. What is the maximum prefix length you can index in MySQL for VARCHAR(255)?
8. How do fulltext indexes work differently from BTREE indexes?
9. What happens if you index every column in a table?
10. Why should you monitor unused indexes?
11. How does EXPLAIN help in query optimization?
12. Can indexes slow down write operations? Why?
13. What are invisible indexes and why would you use them?
14. How do indexes impact storage size?
15. What is the benefit of virtual columns when indexing JSON?
