SQL Professionals Are Withholding These 9 Database Optimisation Tricks From You

Why it functions:

• uses indexes for function-based filtering.
• Ideal for queries that don't care about case
• works with string operations, date manipulation, and other things.
• enables efficient sorting based on calculated values.

-- PostgreSQL syntax (other databases have equivalents)
CREATE INDEX idx_products_category ON products(category_id) 
INCLUDE (name, price, image_url);

-- Now this query can be satisfied entirely from the index
SELECT name, price, image_url 
FROM products 
WHERE category_id = 42;

Why it functions:

• Benefits of covering indexes with smaller key sizes are obtained.
• More suitable for range scan indexes
• maintains the index structure's effectiveness
• minimizes the requirement for numerous specialized indexes

After using this method, the category page load time for a client's product catalog with five million items decreased from 600 ms to 40 ms.

5. The Ability to Explain and Analyze
Not a secret precisely, but terribly underutilized. EXPLAIN ANALYZE displays the precise timing and execution plan for your query:

-- PostgreSQL syntax (other databases have similar commands)
EXPLAIN ANALYZE
SELECT * FROM orders 
WHERE customer_id = 123 
AND created_at > CURRENT_DATE - INTERVAL '30 days';

What you will discover:

• Whether the usage of your indexes is what you expect
• Where is the time being spent?
• The number of rows that are analyzed vs returned
• Whether the sorting is taking place on disk or in memory
• If the optimizer's calculations are accurate

-- PostgreSQL syntax
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT 
    date_trunc('day', created_at) AS day,
    product_id,
    SUM(quantity) AS units_sold,
    SUM(quantity * price) AS revenue
FROM order_items
JOIN orders ON order_items.order_id = orders.id
WHERE orders.status = 'completed'
GROUP BY 1, 2;

-- Refresh when needed
REFRESH MATERIALIZED VIEW daily_sales_summary;

Why it functions:

• calculates costly aggregations and joins in advance.
• Ideal for analytics and reporting inquiries
• able to be indexed similarly to regular tables
• Perfect for data that updates periodically as opposed to continuously

-- Ineffective approach
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status);

-- For a query that filters on both columns
SELECT * FROM orders 
WHERE customer_id = 123 AND status = 'shipped';

-- Effective approach
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

• In a query, the database can typically employ precisely one index per table.
• The index's column order is very important.
• Only one of these single-column indexes is frequently used.
• With the proper column order, multi-column indexes may accommodate a variety of query patterns.

-- Instead of indexing every value, create a filtered index for important ranges
CREATE INDEX idx_logs_recent ON logs(timestamp, level, message) 
WHERE timestamp > CURRENT_DATE - INTERVAL '7 days';

-- Or use a function to reduce cardinality
CREATE INDEX idx_temperature_readings_hour ON sensor_data(
    sensor_id, 
    DATE_TRUNC('hour', timestamp)
);

• Significantly reduced index size
• Improved selection for frequently asked questions
• Lower maintenance costs
• Frequently more effective than attempting to explicitly index high-cardinality columns

-- PostgreSQL declarative partitioning example
CREATE TABLE orders (
    id SERIAL,
    customer_id INTEGER,
    amount DECIMAL(10,2),
    created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (created_at);

-- Create partitions by month
CREATE TABLE orders_202301 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
    
CREATE TABLE orders_202302 PARTITION OF orders
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
    
-- And so on...

• Partition trimming allows queries to completely omit unnecessary partitions.
• Certain partitions may be the focus of maintenance procedures.
• increases the use of cache
• simplifies the process of archiving old data.
• able to divide partitions among several storage levels

SELECT o.id, o.created_at, o.total_amount, o.status, 
       COUNT(i.id) AS item_count
FROM orders o
LEFT JOIN order_items i ON o.id = i.order_id
WHERE o.user_id = 12345
GROUP BY o.id, o.created_at, o.total_amount, o.status
ORDER BY o.created_at DESC
LIMIT 20 OFFSET 0;

• To filter and sort the orders database, use a covering index.
• For columns that are regularly retrieved, include an INCLUDE.
• A materialized view for active users is something to think about.

-- Create an efficient index
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC)
INCLUDE (total_amount, status);

-- Rewrite the query to use window functions instead of grouping
SELECT o.id, o.created_at, o.total_amount, o.status, 
       COALESCE(i.item_count, 0) AS item_count
FROM orders o
LEFT JOIN (
    SELECT order_id, COUNT(*) AS item_count
    FROM order_items
    GROUP BY order_id
) i ON o.id = i.order_id
WHERE o.user_id = 12345
ORDER BY o.created_at DESC
LIMIT 20;

Beyond SQL: When to Take a Look at Other Methods
Sometimes using less SQL is the best way to optimize it:

• Caching: Take into account Redis or application-level caching if data doesn't change often.
• Denormalization: Costly joins can be avoided by strategically duplicating data.
• Examine the replicas: Reporting query offloading to specialized database instances
• specialized databases: Think about document stores for intricate hierarchical data, time-series databases for metrics, etc.

1. Use log analysis or monitoring tools to find slow queries.
2. Learn about access patterns through application code analysis.
3. Use EXPLAIN ANALYZE to determine the true situation.
4. Use focused optimizations that are grounded in actual facts.
5. Analyze outcomes to verify advancements.