MySQL Temporary Table reference limitation

by ·0 Comment

A TEMPORARY table is a table that is bound to a connection and is dropped when that connection is closed. It seems like this would just be a normal table with some meta data attached to it indicating the connection it is bound to. I suppose for some reason it is more complicated than that… seems unnecessarily so…

Anyways, the limitation is that you cannot refer to a given temporary table more than once in a single query.  If you do you will get a “Can’t reopen temporary table” error. So, for instance you cannot insert into a temporary table some data that you select from it in in the same query. This limitation has bit me several times.

The bug is here: http://bugs.mysql.com/bug.php?id=10327

Continue reading

MySQL Stored Procedure Performance Tricks

by ·0 Comment

In our application at ControlPath, we have a pretty complex permission system. Well, imagine a permission system based off of a cyclical graph of 30,000 nodes with multiple levels of inheritance rules and you just begin to scratch the surface. I recently rewrote the permission rebuild caching system into recursive stored procedures. This improved performance from 45 minutes… to 45 seconds. There are a bunch of tricks I employed to get this kind of speedup, but today I am going to share 2 of the simplest ones with you… Continue reading

Bigger isn’t always better for MySQL

by ·0 Comment

Fairly recently, I did some performance tuning on our MySQL servers and discovered some very interesting things.  The previous “DBA” had the mantra of “set the value higher and it will go faster.”  We are running some pretty beefy hardware so I can definitely see where this logic would come in to play.  I became concerned when my little laptop with a default MySQL installation was getting better performance than our 8 gig of Ram, dual processor quad core monster server.

Continue reading