I just finished a project for a client using in part MySQL. The project consisted of bug fixes, new features, and speedup of an existing application written in Python. The software originally used SQLite.

I managed to speed up the most important query by 2000%, and eliminated locking problems (SQLite locks the ENTIRE database against write during a read) which resulted in loss of data when the database was locked for too long. (Hardware devices sending updates, and the server code gives up after 15 seconds.)

To get that kind of improvement, I fiddled with the queries in every which way. One of my more recent brilliant ideas was that - OBVIOUSLY - using stored procedures would be faster.

Right? Stored procedures are obviously faster. Everybody thinks that.

Wrong.

Not in MySQL.

The code had a sub-query that was "hand coded". That is, it did a query on a "parent" table, then looped through the results, doing a query on a "child" table for each.

Now, obviously, that would be faster if I did it all in one query, right?

Not in MySQL.

Maybe I'm just rusty at SQL-slinging. But the best I could do with a single SQL statement with the subquery was 2 seconds for a certain query. With the query-in-a-loop, it's .5 seconds. (The original code with SQLite took 10 seconds - that's with the looped query. No, it wasn't just SQLite vs. MySQL - SQLite is actually pretty darn close to MySQL speed-wise in many ways. It was just plain awful code.)

So, once I figured gave up at coming up with a single statement that was faster than using the loop, I figured... hey, why repeated send that query to the server - put it in a stored procedure!

I actually found an acknowledgment on the MySQL website that stored procedures - at least at present - are significant slower than inline ones. They try to make the case that if you are using stored procedures, you should be doing it to encapsulate business logic - not with an expectation of speeding things up.

Turns out that MySQL doesn't take advantage of query caching when stored procedures are used.

Oh, yea, I can hear you thinking.... prepared statements. And, I'll bet you can already hear my answser:

Not in MySQL. (not faster, that is - they are slower).

Now, I need to make a decision on which database to use on one of my OWN projects. I'd really like to use stored procedures.

Anybody used them in both MySQL and PostGreSQL? I know Postgres has had them longer. Are they also faster?

Or am I fooling myself, and is this still the domain of Oracle alone?

Some rough numbers:

Original/SQLite 10 seconds

MySQL immediately after naive rewrite (just resolve some MySQL syntax differences) - 4 seconds.

MySQL tweaked, still using loop: 0.5 seconds.

MySQL single statement - 2 seconds.

MySQL tweaked, using loop, using a stored procedure: 1 second

For reference, here's a sanitized version of the single statement I cam up with. (Note that I'm omitting a whole buncha WHERE.)

/* Generic design pattern 
 * 
 * You can arbitrarily extend this to "n" widget events */ 
SELECT 
 widget.*, latest_event.*, previous_event.* 
FROM 
 widget 
 LEFT OUTER JOIN 
 widget_event AS latest_event 
 ON 
 ( 
 widget.wid = latest_event.wid AND latest_event.timestamp = 
 ( 
 SELECT 
 timestamp 
 FROM 
 widget_event 
 WHERE 
 wid = widget.wid 
 ORDER BY timestamp DESC 
 LIMIT 1 
 ) 
 ) 
 LEFT OUTER JOIN 
 widget_event AS previous_event 
 ON 
 ( 
 widget.wid = previous_event.wid AND previous_event.timestamp = 
 ( 
 SELECT 
 timestamp 
 FROM 
 widget_event 
 WHERE 
 wid = widget.wid AND timestamp < latest_event.timestamp 
 ORDER BY timestamp DESC 
 LIMIT 1 
 ) 
 ) 
ORDER BY widget.id ASC;