<!-- ========== SCHEMAS ========== -->
<!-- FAQPage schema (generated from translation data) -->
<!-- WebSite schema -->
<!-- LocalBusiness schema -->
?>
<!-- Organization schema -->
<!-- BreadcrumbList schema (headline from translation) -->
<!-- CommunicateAction schema -->
<!-- ========== ARTICLE CONTENT ========== -->
MySQL Indexing: Slash Query Times by Up to 96%
November 2025
14 min read
I’m Jacob Campbell, and the single biggest backend speed win I see is also the most overlooked: indexing. Without the right index, MySQL reads every row in a table to answer a query; with one, it jumps straight to the rows it needs. The difference is routinely a query dropping from a few hundred milliseconds to single digits — in real terms I’ve taken a 340 ms query down to 8 ms. Here’s how indexes work and how to find the ones you’re missing.
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5; -- log queries slower than 0.5 seconds
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
EXPLAIN SELECT * FROM products WHERE category_id = 5 AND price < 100;
CREATE INDEX idx_user_id ON orders (user_id);
-- Slow: 2 seconds, scans 50,000 rows
SELECT * FROM products WHERE category_id = 5 AND price < 100;
-- Add composite index (order matters!)
CREATE INDEX idx_category_price ON products (category_id, price);
-- Now: 30ms, scans 127 rows
-- Query only needs id and name
SELECT id, name FROM products WHERE category_id = 5;
-- Covering index
CREATE INDEX idx_category_id_name ON products (category_id, name);
-- EXPLAIN will show "Using index" in Extra column
SELECT * FROM products
WHERE category_id = 12
AND brand_id IN (3,7,9)
AND price BETWEEN 50 AND 200
ORDER BY price LIMIT 24;
CREATE INDEX idx_category_brand_price ON products (category_id, brand_id, price);
-- If you frequently query postmeta by meta_key and meta_value
ALTER TABLE wp_postmeta ADD INDEX idx_meta_key_value (meta_key, meta_value(100));
CREATE INDEX idx_city_price_beds_type ON properties (city_id, price, bedrooms, property_type);
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;
