Query constructs

Optimize your SQL logic with efficient syntax and by avoiding unnecessary operations.

Avoid Unnecessary ORDER BY

Sorting large datasets is expensive. Only use ORDER BY when necessary—and only on reduced result sets.

Instead of:

SELECT ... 
FROM device_data 
ORDER BY reading_time DESC 
LIMIT 10;

Use:

SELECT ...
FROM device_data 
WHERE reading_time >= '2024-12-20' 
ORDER BY reading_time DESC 
LIMIT 10;

Format Output Last

Delay formatting (e.g., with DATE_FORMAT) until the final result step to preserve optimizer flexibility.

Bad:

WITH formatted AS (
  SELECT DATE_FORMAT(reading_time) AS ts FROM device_data
)
SELECT * 
FROM formatted 
WHERE ts LIKE '2025%';

Better:

SELECT DATE_FORMAT(reading_time) AS ts
FROM device_data
WHERE reading_time BETWEEN '2025-01-01' AND '2026-01-01';

Replace CASE in Filtering or Grouping

Avoid using CASE expressions in filters, joins, or groupings when alternatives exist.

Bad:

SELECT SUM(CASE WHEN value > 10 THEN 1 ELSE 0 END) FROM my_table;

Better:

SELECT COUNT(*) FILTER (WHERE value > 10) FROM my_table;

Prefer GROUP BY Over DISTINCT

Instead of:

SELECT DISTINCT country FROM customers;

Use:

SELECT country FROM customers GROUP BY country;

Use Subqueries When Groups Are Known

If the group keys are known, use correlated subqueries to avoid materializing full group sets in memory.

Instead of:

SELECT customerid, SUM(order_amount) 
FROM customer_orders 
GROUP BY customerid;

Use:

SELECT customerid,
  (SELECT SUM(order_amount) 
   FROM customer_orders 
   WHERE customer_orders.customerid = customers.customerid) 
AS total
FROM customers;