MySQL Performance Comparison: Working With and Without Indexes

In this article, we'll examine how indexes affect query performance in MySQL, comparing the execution of filtering and JOIN operations with and without indexes. What Are Indexes and Why Are They Needed? Indexes in MySQL are special data structures that accelerate data search and retrieval from tables. Without indexes, MySQL has to perform a full table scan, which is similar to reading an entire book to find one word. With indexes, searching becomes similar to using a book's table of contents. Example 1: Data Filtering by a Single Field Setting Up the Test Environment Let's create a test table and populate it with data: -- Create test database CREATE DATABASE test_indexes; USE test_indexes; -- Create table without indexes CREATE TABLE users_no_index ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), email VARCHAR(100), age INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Create similar table with index CREATE TABLE users_with_index ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), email VARCHAR(100), age INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_age (age) ); -- Populate tables with test data (100,000 records each) DELIMITER $$ CREATE PROCEDURE GenerateTestData() BEGIN DECLARE i INT DEFAULT 1; WHILE i <= 100000 DO INSERT INTO users_no_index (name, email, age) VALUES (CONCAT('User', i), CONCAT('user', i, '@example.com'), FLOOR(RAND() * 100)); INSERT INTO users_with_index (name, email, age) VALUES (CONCAT('User', i), CONCAT('user', i, '@example.com'), FLOOR(RAND() * 100)); SET i = i + 1; END WHILE; END$$ DELIMITER ; CALL GenerateTestData(); Filtering Performance Comparison Query without index: -- Analyze query without index EXPLAIN SELECT * FROM users_no_index WHERE age = 25; EXPLAIN Results: type: ALL (full table scan) rows: 100000 (100,000 rows checked) Extra: Using where type: ALL (full table scan) rows: 100000 (100,000 rows checked) Extra: Using where Execution time: ~120 ms Query with index: -- Analyze query with index EXPLAIN SELECT * FROM users_with_index WHERE age = 25; EXPLAIN Results: type: ref (index search) rows: ~1000 (only ~1000 rows checked) key: idx_age (index used) Extra: Using index condition type: ref (index search) rows: ~1000 (only ~1000 rows checked) key: idx_age (index used) Extra: Using index condition Execution time: ~5 ms Filtering Conclusions Without index: MySQL performs a full table scan, checking every row With index: MySQL uses the B-tree index to quickly find the required rows Performance difference: 20-30 times faster Without index: MySQL performs a full table scan, checking every row With index: MySQL uses the B-tree index to quickly find the required rows Performance difference: 20-30 times faster Example 2: JOIN Operations With and Without Indexes Preparing Related Tables -- Orders table without indexes CREATE TABLE orders_no_index ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT, amount DECIMAL(10,2), order_date DATE, status VARCHAR(20) ); -- Orders table with index CREATE TABLE orders_with_index ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT, amount DECIMAL(10,2), order_date DATE, status VARCHAR(20), INDEX idx_user_id (user_id) ); -- Populate orders tables DELIMITER $$ CREATE PROCEDURE GenerateOrderData() BEGIN DECLARE i INT DEFAULT 1; WHILE i <= 50000 DO INSERT INTO orders_no_index (user_id, amount, order_date, status) VALUES (FLOOR(RAND() 100000) + 1, RAND() 1000, DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY), 'completed'); INSERT INTO orders_with_index (user_id, amount, order_date, status) VALUES (FLOOR(RAND() 100000) + 1, RAND() 1000, DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY), 'completed'); SET i = i + 1; END WHILE; END$$ DELIMITER ; CALL GenerateOrderData(); JOIN Operations Comparison JOIN without indexes: -- JOIN without indexes EXPLAIN SELECT u.name, COUNT(o.id) as order_count, SUM(o.amount) as total_amount FROM users_no_index u JOIN orders_no_index o ON u.id = o.user_id WHERE u.age BETWEEN 25 AND 35 GROUP BY u.id, u.name; EXPLAIN Results: For both tables: type: ALL rows: 100000 * 50000 = 5,000,000,000 potential comparisons Extra: Using where; Using temporary; Using filesort For both tables: type: ALL rows: 100000 * 50000 = 5,000,000,000 potential comparisons Extra: Using where; Using temporary; Using filesort Execution time: ~4500 ms JOIN with indexes: -- JOIN with indexes EXPLAIN SELECT u.name, COUNT(o.id) as order_count, SUM(o.amount) as total_amount FROM users_with_index u JOIN orders_with_index o ON u.id = o.user_id WHERE u.age BETWEEN 25 AND 35 GROUP BY u.id, u.name; EXPLAIN Results: For users: type: range (uses age index) For orders: type: ref (uses user_id index) rows: significantly fewer More efficient use of temporary tables For users: type: range (uses age index) For orders: type: ref (uses user_id index) rows: significantly fewer More efficient use of temporary tables Execution time: ~150 ms JOIN Operations Conclusions Without indexes: MySQL is forced to perform nested loops with full table scans With indexes: MySQL efficiently uses indexes to quickly find related records Performance difference: 30 times or more Without indexes: MySQL is forced to perform nested loops with full table scans With indexes: MySQL efficiently uses indexes to quickly find related records Performance difference: 30 times or more When to Use Indexes? Recommended to create indexes for: Fields frequently used in WHERE conditions Fields involved in JOIN operations Fields used for sorting (ORDER BY) Fields used in GROUP BY Unique fields or primary keys Fields frequently used in WHERE conditions Fields involved in JOIN operations Fields used for sorting (ORDER BY) Fields used in GROUP BY Unique fields or primary keys When indexes might be inefficient : Tables with frequent INSERT/UPDATE/DELETE operations Small tables (less than 1000 rows) Columns with low selectivity (few unique values) Tables with frequent INSERT/UPDATE/DELETE operations Small tables (less than 1000 rows) Columns with low selectivity (few unique values) Best Practices for Working with Indexes Index consciously - each index slows down write operations Use composite indexes for frequently used field combinations Monitor selectivity - indexes on fields with few unique values are less effective Regularly analyze index usage : Index consciously - each index slows down write operations Use composite indexes for frequently used field combinations Monitor selectivity - indexes on fields with few unique values are less effective Regularly analyze index usage : -- Analyze index usage SELECT * FROM sys.schema_unused_indexes; 5. Optimize existing indexes : -- Analyze query performance EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age = 25; -- Check index fragmentation ANALYZE TABLE users_with_index; Conclusion Indexes are a powerful tool for optimizing MySQL performance. Proper use can speed up query execution by tens of times, especially for filtering and JOIN operations. However, it's important to remember the balance - excessive indexing can slow down write operations. Regular monitoring and performance analysis will help you find the optimal index configuration for your application. Test with your own data, as index effectiveness heavily depends on your specific data characteristics and query patterns in your application.

Technologies & topics

Article tags

No projects match these filters.

MySQL Performance Comparison: Working With and Without Indexes

Article tags

Have a project or an idea to discuss?