We are at the beginning of a new project, and we are really wondering if we should use stored procedures in MySQL or not.
We would use the stored procedures only to insert and update business model entities. There are several tables which represent a model entity, and we would abstract it in those stored procedures insert/update.
On the other hand, we can call insert and update from the Model layer but not in MySQL but in PHP.
In your experience, Which is the best option? advantages and disadvantages of both approaches. Which is the fastest one in terms of high performance?
PS: It is is a web project with mostly read and high performance is the most important requisite.
Unlike actual programming language code, they:
If you have a very database-specific action (eg an in-transaction action to maintain db integrity), or keep your procedures very atomic and simple, perhaps you might consider them.
Caution is advised when specifying "high performance" up front. It often leads to poor choices at the expense of good design and it will bite you much sooner than you think.
Use stored procedures at your own peril (from someone who's been there and never wants to go back). My recommendation is to avoid them like the plague.