Consultas Anidadas y Vistas Materializadas

Relational database programming offers powerful tools to optimize queries and store information efficiently. In MySQL, although materialized views do not exist as a native feature, we can implement data structures that emulate their operation, allowing us to avoid repeated execution of complex queries and significantly improving the performance of our applications.

How to create structures similar to materialized views in MySQL?

To understand how we can implement something similar to a materialized view in MySQL, we must first remember that a regular view is simply a query that is executed every time we query it. However, what we need is a permanent table that stores the results of a complex query, thus avoiding processing time every time we need that information.

Let's start by creating a table that will store the results of our queries:

CREATE TABLE sales_daily_m ( `date` DATE NOT NULL UNIQUE, `count` INTEGER, total FLOAT);

This simple table will store three fundamental data: the date of the transaction, the number of transactions performed on that date and the total amount of those transactions. The date column will be our unique key to avoid duplicates.

Once the table is created, we can fill it with information using an INSERT combined with a SELECT:

INSERT INTO sales_daily_m (`date`, `count`, total)SELECT DATE(date_add), COUNT(build_product_id), SUM(total)FROM build_productsGROUP BY 1;

This command inserts the data grouped by date into our table, counting the operations and summing the totals. The result is a table containing historical information organized in an efficient way, ready to be consulted without the need to recalculate these values each time.

What are nested queries and how to use them?

Nested queries are a powerful technique that allows us to use the result of one query within another. There are two main ways we can use this technique:

1. When the inner query returns a single value.
2. When the internal query returns a list of values.

Let's look at a practical example: suppose we want to find all entries in build_products that correspond to customers from Argentina and of male gender.

Using JOIN:

SELECT bp.build_product_id, bp.date_add, bp.totalFROM build_products AS bp LEFT JOIN builds AS b ON bp.bill_id = b.build_id LEFT JOIN clients AS c ON b.client_id = c.client_idWHERE c.country = 'AR' AND c.gender = 'M'ORDER BY 2 ASC;

Using nested queries:

SELECT bp.build_product_id, bp.date_add, bp.totalFROM build_products AS bpWHERE bp.bill_id IN ( SELECT b.build_id FROM builds AS b LEFT JOIN clients AS c ON b.client_id = c.client_id WHERE c.country = 'AR' AND c.gender = 'M' )ORDER BY 2 ASC;

Both approaches produce exactly the same result (98 rows in this case), but each has its advantages in different situations. Nested queries are particularly useful when we need to update data based on complex conditions.

How to update data using nested queries?

Nested queries are especially useful for updating data based on complex calculations or conditions. Let's see an example where we add a column to the clients table to store the number of invoices associated to each client:

ALTER TABLE clients ADD COLUMN bill_count INTEGER;

Now, we can update this column using a nested query:

UPDATE clients AS cSET bill_count = ( SELECT COUNT(*) FROM bills AS b WHERE b.client_id = c.client_id);

This command updates the bill_count column for each client with the exact number of bills associated with that client. The power of this approach lies in the fact that the internal query is executed once for each row of the external table, allowing us to perform precise and customized updates.

Advantages of nested queries

Nested queries offer several advantages:

• They allow us to perform complex operations that would be difficult to express with JOINs.
• They are especially useful for conditional updates and deletes.
• They can improve code readability in certain cases.
• They offer flexibility in working with subsets of data.

However, it is important to remember that they are not always the best choice. In some cases, JOINs may be more efficient or clearer.

The techniques we have explored are critical to optimizing the performance of our databases and building efficient applications. Both materialized view-like structures and nested queries allow us to manipulate and access data more efficiently, reducing the load on our system and improving the user experience. Have you used any of these techniques in your projects? Share your experience in the comments.