Sometimes we can use a stored program to perform query or DML operations that perform badly in standard SQL. This usually happens when the “pure” SQL statement becomes overly complex because of limitations in the SQL syntax or when the MySQL optimizer isn’t able to come up with a sensible plan for your SQL query. In this section we offer two scenarios in which a stored program can be expected to outperform a SQL statement that executes the same logical steps.
One situation in which a stored program might offer a better solution is where you are forced to construct a query that joins a table to itself in order to filter for the required rows. For instance, in Example 22-5, we issue a SQL statement that retrieves the most valuable order for each customer over the past few months.
SELECT s.customer_id,s.product_id,s.quantity, s.sale_value FROM sales s, (SELECT customer_id,max(sale_value) max_sale_value FROM sales GROUP BY customer_id) t WHERE t.customer_id=s.customer_id AND t.max_sale_value=s.sale_value AND s.sale_date>date_sub(currdate( ),interval 6 month);
This is an expensive SQL statement, partially because we first
need to create a temporary table to hold the customer ID and maximum
sale value and then join that back to the
sales table to find the full details for
each of those rows.
MySQL doesn’t provide SQL syntax that would allow ...