Case study 1 – an example of how to optimize a complex query

We will explore in this case study a complex step-by-step query such as the following:

1. How to format a complex query
2. Run and analyze an execution plan
3. Optimize a complex query

When we have a query that runs in production and is complex, we need to format it so that we can have more clarity and can easily optimize it.

Here is our request (not formatted):

Here is our formatted query:

Here is the EXPLAIN plan:

Let's analyze the query together.

The first problem observed from the output of the execution plan is the following:

SUBQUERY DEPENDENT (NOT EXISTS ( SELECT 1 FROM employees_party WHERE fk_employees_id = employees.id AND Important_id BETWEEN 1 and 4 AND diff_value BETWEEN 1 and 3));

The solution is to rewrite the part of the SUBQUERY DEPENDENT as follows, in order to optimize our query:

SELECT E....