Refactoring MySQL Cursors

by Roland Bouman

Hi All,

My name is Roland Bouman, and I'm a certification developer for MySQL AB. This is my first post on the O'Reilly database weblog, and I figured it would be nice to start with a technical article about MySQL cursors, a subject I have written about before on my blogger weblog.

The first part of this article explains why cursors are usually unnecessary.
A few common problems with cursors are briefly discussed.
Also, typical stored procedure pattern is described that uses a cursor,
and a demonstration is given that shows how it can be refactored
to an equivalent procedure that uses a single SQL statement instead.

In the second part of this article,
the negative performance implications of using cursors are illustrated with a few benchmarks,
and the cases where a cursor might be useful after all are briefly discussed.

4 Comments

Jason P Sage
2007-01-08 08:26:41
Nice Article. I've always believed cursors were a bit much and that SQL itself is fairly powerful in it of itself.


Also I tend to think that when you stick with SQL itself, versus getting to caught up with DBMS specific implementations - like cursors - that you end up that much more portable across different platforms.

Anne
2007-08-26 00:56:09
It was interesting to read
Igor
2007-11-26 16:46:50
Great article, thanks!
Hey, a minor thing - in the very last code snippet you should probably replace r.id_res with l.id_res.
Vlad GURDIGA
2008-04-10 09:23:37
I never knew that there is something like UPDATE with JOINs, but today it just saved my life!! Greeaaat article!