MySQL 8: Leveraging Functional and Invisible Indexes to Handle Millions of Records

When Traditional Indexes Fail in Real-World Scenarios

I remember back when I was managing a CMS for a major news site; the articles table had hit 25 million rows. Every time the content team ran a report to filter articles by month, the server’s CPU would spike to 90%, even though the created_at column was carefully indexed.

SELECT * FROM articles WHERE YEAR(created_at) = 2024;

The problem is that MySQL becomes “blind” to the index when you wrap a column in a function like YEAR(). Instead of a quick lookup, it has to recalculate the value for every single row among those 25 million records. This results in a Full Table Scan taking 30-40 seconds, frustrating users with endless loading times.

Previously, I usually had to create an additional physical column (Stored Column) just to store the year and then index it. This approach wasted storage and cluttered the code. But since MySQL 8.0, Functional Indexes and Invisible Indexes have provided a much cleaner approach. I used this duo to clean up the database without drastically changing the table structure.

Functional Indexes: Indexing the “Invisible”

Simply put, a Functional Index allows you to index an expression or a function directly. This is a lifesaver when you frequently query data using string processing functions, time functions, or complex JSON data.

Why Is This Method So Fast?

In reality, MySQL creates a hidden virtual column and builds the index tree on it. When you query using that exact expression, the Optimizer automatically picks up this index instead of scanning the entire table.

To solve the year-filtering problem mentioned above, I only needed to run a single command:

ALTER TABLE articles ADD INDEX idx_created_year ((YEAR(created_at)));

Pro tip: You must use double parentheses ((...)). If omitted, MySQL will immediately throw a syntax error because it won’t recognize it as an expression.

Processing JSON Data in a Flash

Suppose you have 500,000 electronic products, each with RAM and CPU configurations stored in a JSON column named specs. Searching for products with ’16GB’ of RAM is usually very slow. With MySQL 8, try indexing the JSON key directly:

ALTER TABLE products ADD INDEX idx_ram_size ((CAST(specs->>"$.ram" AS CHAR(10))));

After applying this, query speeds dropped from 2 seconds to just a few milliseconds. This is a massive difference in a production environment.

Invisible Indexes: “Stealth Mode” for Safe Maintenance

Have you ever wanted to delete an index because you suspected it was redundant but feared it might crash the site? Deleting an index is easy, but if you make a mistake, rebuilding an index on a table spanning dozens of GBs can take hours, paralyzing the system.

An Invisible Index allows you to hide an index from the Optimizer, but MySQL still silently updates it whenever an INSERT or UPDATE command is executed.

A “Pro” Database Cleanup Workflow

Instead of deleting it outright, I always follow a safe 3-step process:

1. Hide the index: Switch it to Invisible status.

   ALTER TABLE orders ALTER INDEX idx_old_status INVISIBLE;

2. Observe: Monitor logs and dashboards for 48 hours. If no queries slow down unexpectedly, this index is truly “dead weight.”
3. Delete permanently: Now you can confidently execute the DROP INDEX command.

If something goes wrong, it takes only 0.1 seconds to make it VISIBLE again—much faster than waiting for a full index rebuild.

Quick Comparison Table

Hard-Won Lessons from the Field

When deploying these new index types, I’ve learned a few golden rules:

• Don’t overuse Functional Indexes: They are essentially hidden columns. Creating too many will bloat the database and slow down bulk INSERT operations.
• Always use EXPLAIN: After creating an index, run EXPLAIN to verify it. If the data type in the query (e.g., string) doesn’t match the Functional Index’s data type (e.g., number), MySQL will ignore your index.
• Test new indexes as Invisible: When adding a new index, create it as INVISIBLE first. Then, you can use the session flag use_invisible_indexes=on to test it privately before making it public to the entire server.

Conclusion

Database optimization isn’t about cramming in as many indexes as possible. A good engineer knows how to use the right index at the right time. Functional Indexes help tackle tricky queries, while Invisible Indexes provide peace of mind during maintenance.

If you’re using MySQL 8 and still struggling with manual Generated Columns or fearing database cleanups, try these two features immediately. They will certainly make your administration tasks much easier!