Using Session Variables in MySQL
One of my favorite hacks right now
The first time I learned about session variables was when I was trying to accomplish something like “find the top things with the n highest values in each group”. The article below gave me the idea (and the reference code) to make it happen.
Now, I’m occasionally using this to sort certain information that is not straightforward with a simple query. In addition, this is a great workaround to correlate information from different tables, where a simple join can’t do the trick. For example, a union of one query for each of two tables, an order by based on desired ranking criteria and session variable(s) for finding correlations or consecutive patterns.
I’m glad to find someone who says this is their favorite hack, per this stackoverflow Q&A below. And to think of it, it is indeed a way to create your own lag function. (From my own experience, response time could be longer than normally preferred if the query is not optimized. But isn’t that the case for all queries?)
Lastly, it looks like a new version of MySQL is going to have a set of window functions. I’m excited for this and can’t wait to use it in action.
