Function Order Optimization

Note: Modern database query optimizers are increasingly sophisticated and may automatically apply many of these optimizations regardless of how the query is written. Always test with EXPLAIN or EXPLAIN ANALYZE for your specific database and data distribution.

The order of functions in WHERE clauses and how functions are applied can significantly impact query performance. Understanding these patterns helps write more efficient queries.

1. Function on Column vs. Constant

Before (Inefficient Query)

SELECT * 
FROM users 
WHERE LOWER(username) = 'john';

After (Optimized Query)

SELECT * 
FROM users 
WHERE username = LOWER('John');

2. Order of Nested Functions

Before (Inefficient Query)

SELECT * 
FROM users 
WHERE LOWER(TRIM(username)) = 'john';

After (Optimized Query)

SELECT * 
FROM users 
WHERE username = TRIM(LOWER(' John '));

3. Functions in LIKE

Before (Inefficient Query)

SELECT * 
FROM users 
WHERE LOWER(username) LIKE 'john%';

After (Optimized Query)

SELECT * 
FROM users 
WHERE username LIKE 'john%' COLLATE NOCASE;

4. Arithmetic Expression Order

Before (Inefficient Query)

SELECT * 
FROM orders 
WHERE (price * quantity) > 100;

After (Optimized Query)

SELECT * 
FROM orders 
WHERE price > 100 / quantity;

5. Functions in JOIN Condition

Before (Inefficient Query)

SELECT * FROM users u
JOIN orders o 
ON LOWER(u.email) = LOWER(o.email);

After (Optimized Query)

SELECT * FROM users u
JOIN orders o 
ON u.email = o.email;

6. WHERE Clause Order (Short-Circuiting)

Before (Inefficient Query)

SELECT * 
FROM users 
WHERE LOWER(username) = 'john'
  AND is_active = 1;

After (Optimized Query)

SELECT * 
FROM users 
WHERE is_active = 1
  AND LOWER(username) = 'john';

7. Functional Index

If you frequently query with a function, consider creating a functional index:

Before (Inefficient Query)

SELECT * 
FROM users 
WHERE LOWER(username) = 'john';

After (Optimized Query)

-- Create functional index
CREATE INDEX idx_users_lower_username 
ON users(LOWER(username));

-- Now this can be fast
SELECT * 
FROM users 
WHERE LOWER(username) = 'john';

8. Multiple Functions Per Row

Before (Inefficient Query)

SELECT * 
FROM products 
WHERE LENGTH(TRIM(name)) > 10;

After (Optimized Query)

SELECT * 
FROM products 
WHERE name > '          ';

Best Practices

1. Apply functions to constants, not columns - column = FUNCTION('value') is better than FUNCTION(column) = 'value'
2. Minimize nested functions - Apply functions once to constants rather than multiple times to columns
3. Use COLLATE for case-insensitive matching - Avoid LOWER() on columns when possible
4. Rearrange arithmetic expressions - Move column operations to one side of comparisons
5. Avoid functions on JOIN columns - Functions prevent index usage in JOINs
6. Put selective conditions first - Highly selective conditions should come first for short-circuit evaluation
7. Consider functional indexes - If you must use functions, index them