Stored Procedures in MySQL
Posted by James Tue, 13 May 2008 00:44:00 GMT
A few days ago I wrote my first MySQL stored procedure.
For one of our applications, we had to make a big database schema change. As a part of this process, we needed to populate a new table with some existing data. However, there needs to be a bit of custom logic because the underlying data representation changed.
The algorithm for this operation is O(kn), where n is the number of rows in the original table and k is a small constant due to the way the data is stored in the original table. The unfortunate fact is that the original table has 3.5 million rows. So, I chose to do everything in a stored procedure.
Writing a stored procedure in MySQL is easy enough. However, the available tools suck. It took me forever to figure out that I have to change the delimiter from ‘;’ to something like ‘$$’ for everything to work. There is no debugger and no print statements. If you write something really complicated, it’s almost impossible to debug.
But the performance of stored procedures is amazing! I calculated that if I didn’t use a stored procedure it would take about 8 hours. When we ran the stored procedure, the whole thing finished in 15 minutes. It peaked at more than 13,000 queries per second! What’s more amazing, was that the application was still live and serving requests normally during the data migration.
Well, needless to say, I’ve become a fan of stored procedures, even though it was a pain to edit and debug.