DEV Community

Cover image for Advanced SQL: Mastering Query Optimization and Complex Joins
bilel salem
bilel salem

Posted on

Advanced SQL: Mastering Query Optimization and Complex Joins

Hello everyone, السلام عليكم و رحمة الله و بركاته

SQL (Structured Query Language) is an essential tool for managing and manipulating relational databases. While basic SQL skills can get you started, advanced SQL techniques can greatly enhance your ability to handle complex queries and optimize database performance. This article delves into advanced SQL topics, focusing on sophisticated query optimization strategies, advanced join types, and the intricacies of SELECT statements.

Advanced Query Optimization Techniques

Optimizing SQL queries is a critical skill for database administrators and developers. Advanced query optimization goes beyond basic indexing and query refactoring to include a range of sophisticated techniques.

1. Query Execution Plans

Understanding the execution plan of a query is crucial for optimization. The execution plan shows how the SQL engine executes a query, revealing potential bottlenecks.

  • EXPLAIN: The EXPLAIN statement provides insights into how a query will be executed, allowing you to identify inefficiencies.

    EXPLAIN SELECT column1, column2 FROM table_name WHERE condition;
    
  • ANALYZE: The ANALYZE statement, used in conjunction with EXPLAIN, executes the query and provides runtime statistics, offering a deeper understanding of the query performance.

    EXPLAIN ANALYZE SELECT column1, column2 FROM table_name WHERE condition;
    

2. Subquery Optimization

Subqueries can sometimes be replaced with more efficient joins or with the WITH clause (Common Table Expressions).

  • Replacing Subqueries with Joins:

    -- Subquery
    SELECT * FROM table1 WHERE column1 IN (SELECT column1 FROM table2);
    
    -- Equivalent Join
    SELECT table1.* FROM table1 INNER JOIN table2 ON table1.column1 = table2.column1;
    
  • Using Common Table Expressions (CTEs):

    WITH CTE AS (
        SELECT column1, column2 FROM table_name WHERE condition
    )
    SELECT * FROM CTE WHERE another_condition;
    

3. Indexing Strategies

Advanced indexing strategies include using composite indexes and covering indexes.

  • Composite Index: Indexes that include multiple columns can speed up queries that filter on those columns.

    CREATE INDEX idx_composite ON table_name (column1, column2);
    
  • Covering Index: An index that includes all the columns retrieved by the query can significantly improve performance.

    CREATE INDEX idx_covering ON table_name (column1, column2, column3);
    

4. Partitioning

Partitioning a large table into smaller, more manageable pieces can improve query performance by limiting the amount of data scanned.

  • Range Partitioning:

    CREATE TABLE orders (
        order_id INT,
        order_date DATE,
        ...
    ) PARTITION BY RANGE (order_date) (
        PARTITION p0 VALUES LESS THAN ('2024-01-01'),
        PARTITION p1 VALUES LESS THAN ('2025-01-01'),
        ...
    );
    
  • Hash Partitioning: Distributes data across a specified number of partitions based on a hash function, providing uniform distribution.

    CREATE TABLE users (
        user_id INT,
        username VARCHAR(255),
        ...
    ) PARTITION BY HASH(user_id) PARTITIONS 4;
    
  • List Partitioning: Divides data into partitions based on a list of values.

    CREATE TABLE sales (
        sale_id INT,
        region VARCHAR(255),
        ...
    ) PARTITION BY LIST (region) (
        PARTITION p0 VALUES IN ('North', 'South'),
        PARTITION p1 VALUES IN ('East', 'West')
    );
    

5. Materialized Views

Materialized views store the result of a query physically and can be refreshed periodically, improving performance for complex queries that are executed frequently.

  • Creating a Materialized View:

    CREATE MATERIALIZED VIEW sales_summary AS
    SELECT region, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY region;
    
  • Refreshing a Materialized View:

    REFRESH MATERIALIZED VIEW sales_summary;
    

Note:

In MySQL, views exist, but materialized views do not exist natively. MySQL supports standard views, which are virtual tables that store the query definition and generate the result set dynamically when queried. However, it does not have built-in support for materialized views, which store the result set physically.

Views in MySQL

Creating a View

You can create a view in MySQL using the CREATE VIEW statement. Here's an example:

CREATE VIEW ActiveCustomers AS
SELECT CustomerID, CustomerName, ContactName, Country
FROM Customers
WHERE Status = 'Active';
Enter fullscreen mode Exit fullscreen mode

This creates a view named ActiveCustomers that includes only active customers from the Customers table. Querying this view looks like:

SELECT * FROM ActiveCustomers;
Enter fullscreen mode Exit fullscreen mode

Updating a View

Views can be updated with the CREATE OR REPLACE VIEW statement:

CREATE OR REPLACE VIEW ActiveCustomers AS
SELECT CustomerID, CustomerName, ContactName, Country
FROM Customers
WHERE Status = 'Active' AND Country = 'USA';
Enter fullscreen mode Exit fullscreen mode

This modifies the ActiveCustomers view to include only active customers from the USA.

Dropping a View

You can remove a view with the DROP VIEW statement:

DROP VIEW ActiveCustomers;
Enter fullscreen mode Exit fullscreen mode

Materialized Views in MySQL

MySQL does not support materialized views natively, but there are workarounds to achieve similar functionality. Here are a couple of methods:

1. Using a Table and Scheduled Updates

One common approach is to create a table that stores the results of the query and update it periodically using scheduled events (cron jobs) or triggers.

Creating the Table

First, create a table to store the results:

CREATE TABLE MaterializedActiveCustomers AS
SELECT CustomerID, CustomerName, ContactName, Country
FROM Customers
WHERE Status = 'Active';
Enter fullscreen mode Exit fullscreen mode
Updating the Table

Use a scheduled event to update the table periodically. This example uses a MySQL event to update the table every hour:

CREATE EVENT UpdateMaterializedActiveCustomers
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    DELETE FROM MaterializedActiveCustomers;
    INSERT INTO MaterializedActiveCustomers
    SELECT CustomerID, CustomerName, ContactName, Country
    FROM Customers
    WHERE Status = 'Active';
END;
Enter fullscreen mode Exit fullscreen mode

This event clears and repopulates the MaterializedActiveCustomers table every hour with the latest active customers.

2. Using Triggers

Another approach is to use triggers to keep the table in sync with the base tables. However, this can become complex and may not be as efficient for large datasets.

Example of Using Triggers

Creating the Table

First, create the table:

CREATE TABLE MaterializedActiveCustomers AS
SELECT CustomerID, CustomerName, ContactName, Country
FROM Customers
WHERE Status = 'Active';
Enter fullscreen mode Exit fullscreen mode
Creating Triggers

Create triggers to keep the materialized table updated:

DELIMITER //

CREATE TRIGGER after_customer_insert
AFTER INSERT ON Customers
FOR EACH ROW
BEGIN
    IF NEW.Status = 'Active' THEN
        INSERT INTO MaterializedActiveCustomers (CustomerID, CustomerName, ContactName, Country)
        VALUES (NEW.CustomerID, NEW.CustomerName, NEW.ContactName, NEW.Country);
    END IF;
END //

CREATE TRIGGER after_customer_update
AFTER UPDATE ON Customers
FOR EACH ROW
BEGIN
    IF OLD.Status = 'Active' AND NEW.Status != 'Active' THEN
        DELETE FROM MaterializedActiveCustomers WHERE CustomerID = OLD.CustomerID;
    ELSEIF NEW.Status = 'Active' THEN
        REPLACE INTO MaterializedActiveCustomers (CustomerID, CustomerName, ContactName, Country)
        VALUES (NEW.CustomerID, NEW.CustomerName, NEW.ContactName, NEW.Country);
    END IF;
END //

CREATE TRIGGER after_customer_delete
AFTER DELETE ON Customers
FOR EACH ROW
BEGIN
    DELETE FROM MaterializedActiveCustomers WHERE CustomerID = OLD.CustomerID;
END //

DELIMITER ;
Enter fullscreen mode Exit fullscreen mode

These triggers will ensure that the MaterializedActiveCustomers table stays updated with changes to the Customers table.

Conclusion

While MySQL supports views, it does not have native support for materialized views. However, you can achieve similar functionality using tables with scheduled updates or triggers. By using these workarounds, you can maintain precomputed results that can be queried quickly, similar to materialized views in other database systems.

Advanced Join Types and Techniques

Joins are fundamental to SQL, allowing you to combine data from multiple tables. Beyond basic joins, advanced join techniques can handle more complex requirements.

1. Self Joins

A self join is a regular join but the table is joined with itself. It is useful for comparing rows within the same table.

SELECT a.employee_id, a.name, b.name AS manager_name
FROM employees a
INNER JOIN employees b ON a.manager_id = b.employee_id;
Enter fullscreen mode Exit fullscreen mode

2. Lateral Joins

The LATERAL join allows subqueries to reference columns from preceding tables in the FROM clause. This is useful for more complex queries.

SELECT a.*, b.*
FROM table1 a
LEFT JOIN LATERAL (
    SELECT *
    FROM table2 b
    WHERE b.column1 = a.column1
    ORDER BY b.column2 DESC
    LIMIT 1
) b ON TRUE;
Enter fullscreen mode Exit fullscreen mode

3. Full Outer Joins with COALESCE

Handling cases where you need a full outer join but want to avoid NULL values in the result.

SELECT COALESCE(a.column1, b.column1) AS column1, a.column2, b.column2
FROM table1 a
FULL OUTER JOIN table2 b ON a.column1 = b.column1;
Enter fullscreen mode Exit fullscreen mode

4. Advanced Join Filters

Applying complex conditions in joins to filter results more precisely.

SELECT a.column1, b.column2
FROM table1 a
INNER JOIN table2 b ON a.column1 = b.column1 AND a.date_column BETWEEN '2023-01-01' AND '2023-12-31';
Enter fullscreen mode Exit fullscreen mode

5. Anti Joins and Semi Joins

These joins are useful for exclusion and inclusion queries respectively.

  • Anti Join: Retrieves rows from the left table that do not have a matching row in the right table.

    SELECT a.*
    FROM table1 a
    LEFT JOIN table2 b ON a.column1 = b.column1
    WHERE b.column1 IS NULL;
    
  • Semi Join: Retrieves rows from the left table where one or more matches exist in the right table.

    SELECT a.*
    FROM table1 a
    WHERE EXISTS (SELECT 1 FROM table2 b WHERE a.column1 = b.column1);
    

Advanced SELECT Statements

The SELECT statement can be extended with advanced features to meet complex data retrieval requirements.

1. Window Functions

Window functions perform calculations across a set of table rows related to the current row, providing powerful analytics capabilities.

  • Row Number:

    SELECT column1, column2, ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_num
    FROM table_name;
    
  • Running Total:

    SELECT column1, column2, SUM(column2) OVER (ORDER BY column1) AS running_total
    FROM table_name;
    
  • Ranking:

    SELECT column1, column2, RANK() OVER (PARTITION BY column1 ORDER BY column2) AS rank
    FROM table_name;
    
  • Moving Average:

    SELECT column1, column2, AVG(column2) OVER (PARTITION BY column1 ORDER BY column2 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg
    FROM table_name;
    

2. Recursive CTEs

Recursive CTEs allow you to perform recursive queries, useful for hierarchical data.

WITH RECURSIVE cte AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
    UNION ALL
    SELECT t.column1, t.column2
    FROM table_name t
    INNER JOIN cte ON t.column1 = cte.column1
)
SELECT * FROM cte;
Enter fullscreen mode Exit fullscreen mode

3. JSON Functions

Modern SQL databases often include functions to handle JSON data, enabling you to store and query JSON documents.

  • Extracting JSON Values:

    SELECT json_column->>'key' AS value
    FROM table_name;
    
  • Aggregating into JSON:

    SELECT json_agg(row_to_json(t))
    FROM (SELECT column1, column2 FROM table_name) t;
    
  • Updating JSON Data:

    UPDATE table_name
    SET json_column = jsonb_set(json_column, '{key}', '"new_value"', true)
    WHERE condition;
    

4. Pivoting Data

Pivoting transforms rows into columns, providing a way to reorganize and summarize data for reporting purposes.

  • Using CASE Statements for Pivoting:

    SELECT
        category,
        SUM(CASE WHEN year = 2021 THEN sales ELSE 0 END) AS sales_2021,
        SUM(CASE WHEN year = 2022 THEN sales ELSE 0 END) AS sales_2022
    FROM sales_data
    GROUP BY category;
    

5. Dynamic SQL

Dynamic SQL allows for the construction and execution of SQL statements at runtime, providing flexibility for complex queries that need to be generated dynamically.

  • Executing Dynamic SQL:

    
    EXECUTE 'SELECT * FROM ' || table_name || ' WHERE ' || condition;
    
  • Using Prepared Statements:

    PREPARE stmt AS SELECT * FROM table_name WHERE column1 = $1;
    EXECUTE stmt('value');
    

Conclusion

Mastering advanced SQL techniques allows you to optimize database performance and handle complex queries with ease. Understanding execution plans, leveraging advanced joins, utilizing sophisticated SELECT statements, and implementing advanced indexing strategies are key to becoming proficient in SQL. By integrating these techniques into your workflow, you can significantly enhance the efficiency and scalability of your database-driven applications.

Advanced SQL skills enable you to tackle complex data manipulation and retrieval tasks, ensuring that your applications can handle large volumes of data efficiently and effectively. Whether you are a database administrator, developer, or data analyst, these advanced SQL techniques will empower you to make the most out of your relational databases, leading to better performance, deeper insights, and more robust applications.

Top comments (18)

Collapse
 
andatki profile image
Andrew Atkinson • Edited

Hi Bilel. Nice post! I’ve recently written a PostgreSQL book that covers advanced SQL, query planning, index design, and uses a Ruby on Rails app for examples and exercises.

After reading through your examples, I’ve realized we didn’t cover recursive CTEs (non recursive CTEs are covered) or lateral joins. I’ll save your post and link to as you introduce those topics nicely.

You did a great job of boiling down all of these topics into short descriptions with examples.

As you mentioned in Postgres we’ve got native materialized views that can be refreshed concurrently and indexed. In the extension ecosystem, there are even extensions to incrementally update materialized views.

Thanks for writing the post!

Collapse
 
bilelsalemdev profile image
bilel salem

You are welcome, Thank you .

Collapse
 
alvfig profile image
Alvaro Figueiredo

Great article, I just learned a bunch of new techniques! I only missed a mention to the use case of ANALYZE statement to update the statistics about the dataset, allowing optimization of WHERE clauses.

Collapse
 
craiginscotland profile image
craig mcinne • Edited

Did you state a product anywhere or are you picking pure ANSI standard SQL ?
Looks like SQL Server to me.

Collapse
 
bilelsalemdev profile image
bilel salem • Edited

standard SQL, i put some notes for mysql about materialized views

Collapse
 
ihebsebai profile image
iheb-sebai

Extremely helpful

Collapse
 
firas_latrach profile image
Firas Latrach

Good job 👏

Collapse
 
solixzsys profile image
solixzsys

Thanks very much.
But are this sql construct portable across all major relational databases because you only took notes of MySQL?

Collapse
 
bilelsalemdev profile image
bilel salem

i took note for mysql because it doesn't support directly materialized views but postgres for example supports materialized views . It's more general than specific db .

Collapse
 
alexandrefuente profile image
alexandrefuente

Awesome article. 👏🏽

Collapse
 
ali-cherif profile image
MOHAMMEDI

Very interresting informations

Collapse
 
martinbaun profile image
Martin Baun

Very well done. I've got some videos on YouTube on the same topic as well, and I agree with a lot of this!

Collapse
 
ultimate_solutions_3a629a profile image
Ultimate Solutions

Great