Optimization of stored function overhead

[Beat Vontobel]

Hi

While writing my article on how to emulate ADD_TO_SET() and REMOVE_FROM_SET() with stored functions in MySQL 5.0 (http://www.futhark.ch/mysql/109.html) I realized how tremendous the overhead for calling a stored function actually is.

As I use quite a bit of mathematical expressions in my queries I always planned to encapsulate them in stored functions as soon as I have 5.0 running on our production servers. It saves a lot of typing, gives nicer queries and is much less error-prone. But a simple expression encapsulated in a stored function is about 50 times slower compared to the same inline code, so I have to rethink this.

But actually this overhead could be minimized if MySQL would convert these simple stored functions back to inline code (something like the MERGE algorithm for VIEWs) on the level of the query parser/optimizer. This would be very easy for functions being DETERMINISTIC and using NO SQL, but could be done as well for some functions accessing tables by rewriting them as JOINs.

Does anybody know if something like this is already done (doesn't seem from the performance I get) or is in the pipeline? If not, I think I'd like to add a feature request to the bug database.

Thanks,
Beat Vontobel