Alex Stetsenko

People often say that motivation doesn’t last. Well, neither does bathing – that’s why we recommend it daily.

Zig Ziglar, motivation speaker.

Roland Bouman published his presentation for the MySQL User Conference and Expo 2010 on optimizing MySQL stored routines. His recommendations are based on test measurements using benchmark() function.

The summary is short but could be very useful.

It is a bit confusing that MySQL function supports return statement but stored procedure does not.

However there is LEAVE statement which can serve the same goal:

this statement is used to exit the flow control construct that has the given label. It can be used within BEGIN … END or loop constructs (LOOP, REPEAT, WHILE).

For example the stored procedure below returns the passed number only if it is non-zero:

DROP PROCEDURE IF EXISTS TestProc;
CREATE PROCEDURE TestProc
(
  vTestNum INT
)
TOP:BEGIN
 
IF vTestNum = 0 THEN
  LEAVE TOP;
END IF;
 
SELECT vTestNum;
 
END;

There are many restrictions on stored routines and triggers. However it turned out though a query is fallen under the restrictions it may not cause an error message but simply work a different way. So developers may not be aware that their queries do not work as expected.

For example, inserts cannot be delayed within stored procedures BUT INSERT DELAYED syntax is accepted. The statement is handled as a normal INSERT though.

Working on the task which involved high insert load, we debated either utilize delayed insert or regular one. Later it turned out that delayed insert was not even an option. Thus stay on guard and don’t let MySQL to confuse you. 🙂

In Mountain View, California there’s a building on the Google campus with an Android statue in front of it, positioned among dessert-themed sculptures that illustrate the major platform releases to date.

The Dropdown Check List is extremely nice and stable jQuery widget developed by Adrian Tosca. It transforms a regular select HTML element into a dropdown checkbox list.

Check out the demo page or go immediately to the download one.

Ronald Bradford has shared slides for his presentation at FOSDEM 2010. This presentation covers monitoring, reviewing and analyzing SQL, the art of indexes, improving SQL, storage engines and caching.

My favorite slide is #46:

The best performance improvement for an SQL statement is to eliminate it.

🙂

It seems like Cory Foy has invented a new IT title while he was criticizing recently announced Certified Scrum Developer Program:

CTISTAC – Certified That I Sat Through A Class

🙂

P.S.: hundredth post!