<h1>MySQL Performance Comparison: Working With and Without Indexes</h1>
<p>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.</p>
<h2>What Are Indexes and Why Are They Needed?</h2>
<p>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.</p>
<h3>Example 1: Data Filtering by a Single Field</h3>
<p><strong>Setting Up the Test Environment</strong></p>
<p>Let's create a test table and populate it with data:</p>
<div class="code">-- 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();</div>
<h3>Filtering Performance Comparison</h3>
<p><strong>Query without index:</strong></p>
<div class="code">-- Analyze query without index
EXPLAIN SELECT * FROM users_no_index WHERE age = 25;</div>
<p><strong>EXPLAIN Results:</strong></p>
<ul>
<li>type: ALL (full table scan)</li>
<li>rows: 100000 (100,000 rows checked)</li>
<li>Extra: Using where</li>
</ul>
<p><strong>Execution time:</strong> ~120 ms</p>
<p><strong>Query with index:</strong></p>
<div class="code">-- Analyze query with index
EXPLAIN SELECT * FROM users_with_index WHERE age = 25;</div>
<p><strong>EXPLAIN Results:</strong></p>
<ul>
<li>type: ref (index search)</li>
<li>rows: ~1000 (only ~1000 rows checked)</li>
<li>key: idx_age (index used)</li>
<li>Extra: Using index condition</li>
</ul>
<p><strong>Execution time:</strong> ~5 ms</p>
<p><strong>Filtering Conclusions</strong></p>
<ul>
<li><strong>Without index:</strong> MySQL performs a full table scan, checking every row</li>
<li><strong>With index:</strong> MySQL uses the B-tree index to quickly find the required rows</li>
<li><strong>Performance difference:</strong> 20-30 times faster</li>
</ul>
<h3>Example 2: JOIN Operations With and Without Indexes</h3>
<p><strong>Preparing Related Tables</strong></p>
<div class="code">-- 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();</div>
<h3>JOIN Operations Comparison</h3>
<p><strong>JOIN without indexes:</strong></p>
<div class="code">-- 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
<p><strong>EXPLAIN Results:</strong></p>
<ul>
<li>For both tables: type: ALL</li>
<li>rows: 100000 * 50000 = 5,000,000,000 potential comparisons</li>
<li>Extra: Using where; Using temporary; Using filesort</li>
</ul>
<p><strong>Execution time:</strong> ~4500 ms</p>
<p><strong>JOIN with indexes:</strong></p>
<div class="code">-- 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
<p><strong>EXPLAIN Results:</strong></p>
<ul>
<li>For users: type: range (uses age index)</li>
<li>For orders: type: ref (uses user_id index)</li>
<li>rows: significantly fewer</li>
<li>More efficient use of temporary tables</li>
</ul>
<p><strong>Execution time:</strong> ~150 ms</p>
<p><strong>JOIN Operations Conclusions</strong></p>
<ul>
<li><strong>Without indexes:</strong> MySQL is forced to perform nested loops with full table scans</li>
<li><strong>With indexes:</strong> MySQL efficiently uses indexes to quickly find related records</li>
<li><strong>Performance difference:</strong> 30 times or more</li>
</ul>
<h3>When to Use Indexes?</h3>
<p><strong>Recommended to create indexes for:</strong></p>
<ul>
<li>Fields frequently used in WHERE conditions</li>
<li>Fields involved in JOIN operations</li>
<li>Fields used for sorting (ORDER BY)</li>
<li>Fields used in GROUP BY</li>
<li>Unique fields or primary keys</li>
</ul>
<p><strong>When indexes might be inefficient</strong>:</p>
<ul>
<li>Tables with frequent INSERT/UPDATE/DELETE operations</li>
<li>Small tables (less than 1000 rows)</li>
<li>Columns with low selectivity (few unique values)</li>
</ul>
<h3>Best Practices for Working with Indexes</h3>
<ul>
<li><strong>Index consciously</strong> - each index slows down write operations</li>
<li><strong>Use composite indexes</strong> for frequently used field combinations</li>
<li><strong>Monitor selectivity</strong> - indexes on fields with few unique values are less effective</li>
<li><strong>Regularly analyze index usage</strong>:</li>
</ul>
<div class="code">-- Analyze index usage
SELECT * FROM sys.schema_unused_indexes;</div>
<p>5. <strong>Optimize existing indexes</strong>:</p>
<div class="code">-- Analyze query performance
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age = 25;</div>
<div class="code">-- Check index fragmentation
ANALYZE TABLE users_with_index;</div>
<h2>Conclusion</h2>
<p>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.</p>
<p>Test with your own data, as index effectiveness heavily depends on your specific data characteristics and query patterns in your application.</p>