MySQL Stored Procedure Programming

Stored Programs as an Alternative to Expensive SQL

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.

Avoid Self-Joins with Procedural Logic

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.

Example 22-5. Finding the maximum sale for each customer

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 ...