I have a REST architecture, running PHP on the server side which store and query a Mysql database.
I am re-evaluating one architecture design decision:
DECISION to re-evaluate:
In an attempt to avoid back and forth communications overhead between PHP and Mysql:
For some browser requests (for example: 'GET /sessions/4'), one request requires a big response with related objects links.
For these situations, I have designed some big mysql stored procedures to query multiple tables. Therefore, for one browser request, PHP launch this one Mysql stored procedure which execute. On PHP result reception, my PHP code goes through each Packet of query results to create objects and link them logically.
At the end of this process, PHP query my internal logical view objects to construct the formatted response returned to the browser request.
The alternative would be to request multiple stored procedure and launch each of them within PHP instead of within this one stored procedure.
PROBLEMS: It becomes quite hard to manage changes over time. These stored procedure becomes hard to maintain cause each table change, have an impact on one of them.
QUESTION: I have started this design, because many code generated framework are being criticized for their lack of efficiency on communication between the PHP and Mysql layer. Doing those stored procedure by hand, I expected to have the best performance in production, however, the maintenance start to be annoying. Is real life high efficiency architecture attempts to limit the number of PHP-(Mysql stored procedure) back and forth communication or am I attempting to surround a false problem ???