Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 3

Displaying posts with tag: stored functions (reset)

Implicit Commit Functions?
+1 Vote Up -0Vote Down

Somebody asked about the possibility of putting DML statements inside MySQL stored functions. DML statements like the INSERT, UPDATE, and DELETE. When I said, “Yes, you can put DML statements inside functions.” They showed me the error they encountered, which is only raised at compilation when you put an explicit COMMIT statement or a Data Definition Language (DDL) statement (CREATE, ALTER, DROP, or RENAME) inside a MySQL function. The actual error message displayed is:

ERROR 1422 (HY000): Explicit OR implicit commit IS NOT allowed IN stored FUNCTION OR TRIGGER.

While an explicit COMMIT is obvious when placed inside a function, the implicit COMMIT statement isn’t obvious unless you know a DDL

  [Read more...]
Next Week’s MySQL Sessions at ODTUG Kaleidoscope
+3 Vote Up -1Vote Down

By now you know that there is a MySQL Track during next week’s ODTUG Kaleidoscope in Washington, DC. Ronald Bradford and I organized the schedule at the last minute (Ronald did a lot of the work!). It was difficult to fill a schedule with 19 sessions that are either 1 hour or 1.5 hours long, and to do it I ended up with three presentations.

At each presentation I will be giving away a copy of The MySQL Administrator’s Bible, so be sure to show up! All MySQL track sessions are in Maryland C, and all times are Eastern.

On Monday, June 28th from 4 pm – 5:30 pm I will be presenting

  [Read more...]
A micro-benchmark of stored routines in MySQL
+1 Vote Up -0Vote Down

Ever wondered how fast stored routines are in MySQL? I just ran a quick micro-benchmark to compare the speed of a stored function against a "roughly equivalent" subquery. The idea -- and there may be shortcomings that are poisoning the results here, your comments welcome -- is to see how fast the SQL procedure code is at doing basically the same thing the subquery code does natively (so to speak).

Before we go further, I want to make sure you know that the queries I'm writing here are deliberately mis-optimized to force a bad execution plan. You should never use IN() subqueries the way I do, at least not in MySQL 5.1 and earlier.

I loaded the World sample database and cooked up this query:

PLAIN TEXT SQL:
  • SELECT
  •   [Read more...]
    Showing entries 1 to 3

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.