Rownum Simulation with MySQL

I recently had the need to SELECT some data from MySQL tables and include a “rownum” column on each row. For example, I needed some results that looked like this:

id rownum name
1234 1 Billy
8274 2 Carl
4352 3 Dave

Notice that I cannot use the “id” column because it is auto-generated, and the data is not inserted into the table in the order I am SELECTing it. This type of thing is very easy to achieve in Oracle, which has a built-in “rownum” column that can be added to the SELECT statement. MySQL, unfortunately, does not have a similar construct. So what are we to do?

My first attempt to solve this problem came from a couple of sources like this one:

This solution is quite brilliant. It uses a user defined variable that is incremented each time a row is processed. My query would look something like this:

SELECT id, @rownum:=@rownum+1 AS rownum, name
FROM users u,
(SELECT @rownum:=0) r

This works quite well, but it has a couple of drawbacks. The first is manageable: user defined variables are connection-scoped, so you’ll need to be careful not to overwrite any other variables or reuse this one by accident in another query. Unfortunately, the second drawback is a doozy, and it is documented on the MySQL website. Here is the excerpt of interest:

As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement. In SELECT @a, @a:=@a+1, …, you might think that MySQL will evaluate @a first and then do an assignment second. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an execution plan with a different order of evaluation.

What does that mean? Well, it means you can’t assign a variable and SELECT it in the same statement or things might get out of order. This is because the variable is assigned in the order that rows are processed, which can change depending on how MySQL decides to optimize and process the query. The more complicated your query, the more likely things are to get fuzzy. In my not-so-simple case, I was doing multiple JOINs and an ORDER BY. Everything worked fine on our testing server. I ran the query multiple times, on different days, using the same data as our production server, and even on our production server itself. It worked great in all cases, so we deployed it to production a few days later. Then our website exploded. Nothing was in order. I tracked it down to this query, and more specifically, to this @rownum solution.

So…what happened?

Between the time the query was tested and the time we deployed it, the size of the table in question grew two- or three-fold. This changed MySQL’s execution plan and the order that it processed the rows. My data ended up looking something like this:

id rownum name
1234 3 Billy
8274 1 Carl
4352 2 Dave

In other words, my data was still in the order I wanted, but the row numbers were completely off!

…but don’t fret! Would I be writing this blog post if I didn’t have a solution? Of course not!

The solution to this problem is simple. We just need to shield the assignment of our @rownum variable from the MySQL execution plan. To accomplish that, we move the main query into a subquery like so:

SELECT, @rownum:=@rownum+1 AS rownum,
    SELECT id, name
    FROM users
    ORDER BY name
) u,
(SELECT @rownum:=0) r

Now we’re back to the data we originally expected:

id rownum name
1234 1 Billy
8274 2 Carl
4352 3 Dave

I hope this saves you all the frustration it caused me. 🙂

Including External JavaScript and Stylesheets in a .NET Page

I’ve seen several ASP.NET developers architect their web apps so that the only way they can include page-specific CSS or JavaScript is via the code-behind file. That is a bad method because you generally want to keep as much display logic in the aspx as possible. It is actually very easy to structure your master page to allow page-specific CSS/JavaScript. You’ll kick yourself when you see this:

The Master Page

<%@ Master Language="C#" Inherits="System.Web.Mvc.ViewMasterPage" %>

<html xmlns="">
        <title><asp:ContentPlaceHolder ID="Title" runat="server" /></title>
        <meta http-equiv="Content-type" content="text/html; charset=utf-8" />
        <link rel="Stylesheet" type="text/css" href="/layout.css" />
        <asp:ContentPlaceHolder ID="Head" runat="server" />

The Page

<%@ Master Language="C#" Inherits="System.Web.Mvc.ViewMasterPage" MasterPageFile="~/Views/Shared/Site.Master" %>

<asp:Content ContentPlaceHolderID="Title" runat="server">
    This is my page!

<asp:Content ContentPlaceHolderID="Head" runat="server">
    <link rel="Stylesheet" type="text/css" href="/css/home.css" />
    <script type="text/javascript" src="/scripts/home.js"></script>

Yep…it’s that easy!