Anime GIF

Redundant Conditions and Functional Indexes in MySQL

January 14, 2026

Here’s something I wish someone had casually told me earlier in my MySQL journey, preferably over tea and not after I’d already shipped a slow query to production.

Indexes are amazing… until you accidentally blindfold them.

And one of the easiest ways to do that is by wrapping indexed columns in functions.

When indexes quietly stop working

You add an index. You feel responsible. Grown. Maybe even powerful.

CREATE INDEX idx_due_date ON todos(due_date);

Life is good.

Then a few weeks later you write something like:

SELECT *
FROM todos
WHERE ADDTIME(due_date, due_time)
      BETWEEN NOW() AND NOW() + INTERVAL 1 DAY;

Looks innocent. Reads nicely. Very expressive.

And your index just… sits there. Useless. Like gym equipment turned into a clothes rack.

Because the moment you do ADDTIME(due_date, due_time), MySQL can’t use the due_date index anymore. You didn’t remove it. You didn’t drop it. You obfuscated it.

You basically said: “Hey MySQL, don’t look at the column. Run this function on every row and then we’ll talk.”

So MySQL does exactly that. Full scan. Pain. Regret.

This is what people usually mean when they talk about index obfuscation. You didn’t lose the index. You just made it impossible to use.

Functional indexes: the obvious but heavier fix

One fix is functional indexes.

CREATE INDEX idx_due_datetime 
ON todos ((ADDTIME(due_date, due_time)));

Now MySQL can index the result of the function. Which is great. This is the clean, explicit, “I know what I’m doing” solution.

But sometimes:

  • you’re on a version that doesn’t support it well
  • you can’t touch schema
  • migrations are scary
  • production is fragile
  • or you just want to be sneaky in a good way

That’s where redundant and approximate conditions quietly become one of my favorite little tricks.

Redundant conditions: useless logically, useful physically

A redundant condition is something that doesn’t change the result set at all.

Like:

WHERE id <= 5 AND id <= 10

The id <= 10 does absolutely nothing.

Logically useless. Physically? Sometimes very useful.

Because optimizers don’t care if a condition is “conceptually pointless.” They care if it helps narrow the search space using an index.

So the game becomes:

Can I add a condition that doesn’t change the answer, but does let MySQL use an index?

That’s where approximate conditions enter.

The todo example, but the “aha” version

We want: “todos due in the next 24 hours.”

We wrote:

WHERE ADDTIME(due_date, due_time)
      BETWEEN NOW() AND NOW() + INTERVAL 1 DAY;

Correct. Clean. Slow.

Now notice something subtle.

If something is due in the next 24 hours, its due_date must be either today or tomorrow.

That statement is broader, but never false.

So we can add:

AND due_date BETWEEN CURRENT_DATE 
                AND CURRENT_DATE + INTERVAL 1 DAY

Full query:

SELECT *
FROM todos
WHERE ADDTIME(due_date, due_time)
      BETWEEN NOW() AND NOW() + INTERVAL 1 DAY
  AND due_date BETWEEN CURRENT_DATE 
                  AND CURRENT_DATE + INTERVAL 1 DAY;

This second condition is:

  • logically redundant (it doesn’t exclude any real matches)
  • approximate (it may include false positives)
  • indexable (and that’s the whole point)

Now MySQL can use idx_due_date to zoom into a much smaller slice of the table first.

After that, it runs the expensive ADDTIME(...) check only on that smaller set to remove the false positives.

Same result. Way less work.

This is one of those optimizations that feels illegal the first time you do it.

You’re basically helping the optimizer cheat.

What’s really happening under the hood

You’re splitting the problem into two phases:

  1. Cheap, index-friendly filter “Give me rows that are probably relevant.”

  2. Expensive, exact filter “Now let’s be precise.”

Databases love this. CPUs hate full scans. Indexes exist exactly for this kind of narrowing.

And the beautiful part: you didn’t touch the schema. No migrations. No locks. No late-night Slack apologies.

Why this stuff is fun (and dangerous)

You won’t see this in most tutorials. And you probably shouldn’t reach for it first.

But when you’re deep in a real system, with real constraints, and a real query that refuses to use your beautiful index… this technique feels like discovering a hidden lever behind a bookshelf.

Not common. But when it fits, it fits hard.

It forces you to think less like “how do I write the correct query?” and more like “how does the database physically search for rows?”

And that shift, from logical thinking to physical thinking, is where query optimization actually starts getting interesting.

Final thought

Functional indexes are the clean solution. Avoiding index obfuscation is the smart habit. But redundant and approximate conditions are the crafty backup move.

They’re the duct tape in your backpack. You don’t use it every day. But when something’s rattling in production, you’re very glad it’s there.

If you ever catch yourself staring at EXPLAIN like it personally betrayed you… try asking:

“What true thing can I add that helps the index do its job?”

Sometimes the fastest queries are built on technically useless sentences.

And that’s kind of beautiful.

LinkedIn
X