Avoid Functions in WHERE Clauses

Using functions like LOWER(), TRIM(), or CAST() directly in a WHERE clause can severely degrade SQL performance. These operations prevent the query planner from using indexes, forcing a full table scan instead. This is a common pitfall in filtering logic—especially when developers attempt to normalize input directly in the query.

In the example below, wrapping email in a LOWER() function disables index usage on that column. Even if an index exists, most relational databases (PostgreSQL, MySQL, SQL Server) won't leverage it because the column has been transformed. As a result, the database evaluates the function for every row, which becomes costly at scale.

To optimize performance, always try to compare raw, unmodified column values when filtering. Normalize your input in the application layer (e.g., downcasing emails before storing them), or create indexed computed/generated columns where available. This small change can make a dramatic impact—yielding 2x to 20x faster queries depending on table size.

Galaxy automatically flags function-wrapped columns in filters and suggests corrections. By avoiding functions in WHERE clauses, you maintain index usability and dramatically improve read performance across large datasets.