MySQL stored programs can often add to application functionality and developer efficiency, and there are certainly many cases where the use of a procedural language such as the MySQL stored program language can do things that a nonprocedural language like SQL cannot. There are also a number of reasons why a MySQL stored program approach may offer performance improvements over a traditional SQL approach:
SQL is a declarative, nonprocedural language: this means that in SQL you don’t specify how to retrieve data—you only specify the data that you want to retrieve (or change). It’s up to MySQL itself—specifically, the MySQL query optimizer—to determine how to go about identifying the result set.
From time to time, we might have a very good idea about the most efficient way to retrieve the data, but find that the MySQL optimizer chooses another—less efficient—path.
When we think we know how the data should be retrieved but can’t get the optimizer to play ball, we can sometimes use MySQL stored programs to force the desired approach.
In a traditional SQL-based application, SQL statements and data flow back and forth between the client and the server. This traffic can cause delays even when both the client and the server programs are on the same machine. If the client and server are on different machines, then the overhead is even higher.
We can use MySQL stored programs to eliminate ...