Posted inMySQL

MySQL Prefix Indexes: The ‘Weight Loss’ Secret for Indexes Without Sacrificing Speed

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

When Indexes are No Longer a “Miracle Cure” for Your Database

It is a common habit for many developers to index every column that appears in a WHERE clause. However, if you apply this mechanically to long data fields like URLs, emails, or TEXT, the system will soon pay a heavy price, as often seen in complex MySQL E-commerce Schema Design projects.

I once witnessed a system crash at midnight because the hard drive was 100% full. Strangely, the number of records hadn’t spiked. The main culprit was a request_url column (averaging 200 characters) that was fully indexed. The .ibd file bloated tremendously, slowing down INSERT commands because MySQL struggled to update the massive Index tree every time new data arrived, which is why optimizing MySQL storage is vital.

Prefix Indexes are the solution to this problem. Instead of storing the entire string, we only index the first few characters. This approach strikes a perfect balance between search speed and storage capacity.

Why are Prefix Indexes Extremely Valuable?

Imagine storing a URL 255 characters long. With 1 million rows, a standard index would take up about 250MB. If you use a 20-character Prefix Index, this number drops to about 20MB. That’s a massive difference, especially when optimizing my.cnf for MySQL 8 to better manage memory!

Practical Benefits:

  • Fits in RAM: Smaller indexes make it easier for MySQL to store them entirely in the buffer pool, minimizing Disk I/O.
  • Faster Data Writing: INSERT and UPDATE operations are faster due to the lightweight B-Tree structure.
  • Bypassing Limits: InnoDB has an index key length limit (usually 767 bytes). Prefix Indexing is the only way to index TEXT or BLOB columns.

How to Find the Optimal Prefix Length (The Sweet Spot)

Choosing the prefix length (N characters) is an art. If N is too short, many records will share the same prefix, forcing MySQL to perform more manual data scans. If N is too long, we waste resources.

To find the ideal N, rely on Selectivity. The goal is to achieve a separation degree close to a full index but with the fewest characters possible.

For example, with a customers table of 100,000 rows, let’s check the selectivity of the email column:

-- Maximum selectivity (full column)
SELECT COUNT(DISTINCT email) / COUNT(*) FROM customers; -- Assume the result is 0.9999

Next, experiment with different lengths:

SELECT 
  COUNT(DISTINCT LEFT(email, 7)) / COUNT(*) AS sel_7,
  COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10,
  COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) AS sel_12
FROM customers;

If sel_10 reaches 0.98 (98% selectivity) and sel_12 reaches 0.99, I would choose N=10. Sacrificing 1% selectivity to save over 60% of index space is a fantastic deal.

Implementing Prefix Indexes

The syntax is very straightforward. You just add the number of characters after the column name in the SQL statement.

1. Declare when creating a table

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255),
    INDEX (product_name(10)) -- Take only the first 10 characters
);

2. Update an existing table

ALTER TABLE customers ADD INDEX idx_email_prefix (email(12));

Crucial Caveats for Real-world Implementation

Although powerful, Prefix Indexes aren’t a magic wand for every case. There are 3 points you must never forget:

First: Useless for ORDER BY and GROUP BY. MySQL cannot use a Prefix Index for sorting. If you run ORDER BY email, MySQL is forced to use filesort (disk-based sorting) because the index only contains partial data, which is insufficient to determine the exact order.

Second: The Character Set Trap. In MySQL, limits are measured in bytes, but declarations use characters. With the utf8mb4 character set, each character can take up to 4 bytes. Therefore, email(10) might consume up to 40 bytes of actual memory.

Third: Always check with EXPLAIN. Don’t guess. Run EXPLAIN to ensure the MySQL Optimizer isn’t ignoring your index. If selectivity is too low, MySQL would rather perform a Full Table Scan than read the index and then have to look back at the main table.

EXPLAIN SELECT * FROM customers WHERE email LIKE 'dev@%';

Conclusion

Database optimization is about finding the balance between speed and resources. Prefix Indexes help keep your system lean, preventing index bloat from clogging your infrastructure. If you are handling tables with millions of rows, try applying this technique immediately, perhaps along with optimizing MySQL pagination. The results in terms of speed and freed-up disk space will surely surprise you.

Share:
Tags: