DEV Community

Vishad Patel
Vishad Patel

Posted on

MySQL & PostgreSQL Performance: The Hidden Cost of Using Functions on Indexed Columns

#database #postgres #mysql #performance

🚨 Problem:

A common mistake in SQL queries is applying functions like LOWER(), UPPER(), TRIM(), or DATE() directly on indexed columns. When you do this, the database engine cannot use the index efficiently, because the expression inside the WHERE clause no longer matches the indexed column. This leads to full table scans, increased CPU usage, and slow response times.

Example of an inefficient query:

SELECT * FROM users 
WHERE LOWER(email) = 'admin@example.com';
Enter fullscreen mode Exit fullscreen mode

Even if there's a standard index on the email column, this query bypasses the index because the function wraps the column, changing how it's evaluated.

✅ Optimized Solution: Use Functional Indexes or Preprocessed Columns

To retain the benefit of indexing while still performing function-based lookups, you should create an index on the expression itself (called a functional or expression-based index, depending on the RDBMS):

-- PostgreSQL or Oracle-style functional index
CREATE INDEX idx_lower_email ON users(LOWER(email));

-- Optimized query

SELECT * FROM users 
WHERE LOWER(email) = 'admin@example.com';
Enter fullscreen mode Exit fullscreen mode

Now, the database uses the index because it matches the expression exactly.

🔍 Note: MySQL (prior to 8.0) does not support functional indexes directly. In those cases, you can store a preprocessed column (email_lower) and index that instead.

⚙️ Technical Insight:

When a function like LOWER(email) is used in a query:

  • The database must apply the function to every row in the table
  • This prevents index seek and forces an index scan or table scan
  • On large tables (e.g., millions of users), this can be 10x slower than using a proper functional index

With a functional index, the expression is precomputed and indexed, so the database can use a fast B-tree lookup instead of evaluating the function at runtime.

📈 Real-Life Use Case:

Imagine a high-traffic login system where user emails are matched case-insensitively:

SELECT * FROM users 
WHERE LOWER(email) = LOWER($1);
Enter fullscreen mode Exit fullscreen mode

Without functional indexing, this becomes a bottleneck:

  • Slower logins under high concurrency
  • Backend latency spikes
  • Increased DB load during peak hours

With a LOWER(email) index:

  • Instant case-insensitive lookups
  • Fast auto-suggestions for email fields
  • Secure and consistent login UX

🧠 Advanced Tip:

In PostgreSQL:
Use citext (case-insensitive text) if many columns require case-insensitive comparisons
Or combine expression indexes with UNIQUE constraints for login validation:

CREATE UNIQUE INDEX idx_lower_email_unique ON users(LOWER(email));
Enter fullscreen mode Exit fullscreen mode

💡 Bonus Thought:

Functional indexes aren't just for text. They're powerful for:

  • Date truncation: DATE(created_at)
  • Numeric rounding: ROUND(price, 2)
  • Normalized values: TRIM(LOWER(name))

Always profile your queries using EXPLAIN to confirm index usage and avoid silent performance hits.

If you found these SQL join optimization techniques helpful, please give this post a like! For a deeper dive into each technique, including real-world query fixes and more examples, I invite you to read the full article on Medium: 10 SQL Join Optimization Techniques Every Backend Developer Should Know. Don't forget to subscribe to my Medium for more backend development insights!

If you enjoyed this article, don’t forget to like ❤️, share 🔄, and subscribe 🔔 so you never miss future updates!

Top comments (0)