Home > MySQL > Rownum Simulation with MySQL

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: http://jimlife.wordpress.com/2008/09/09/displaying-row-number-rownum-in-mysql/.

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
ORDER BY name

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 u.id, @rownum:=@rownum+1 AS rownum, u.name
FROM (
    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. :)

About these ads
Categories: MySQL
  1. Mathieu
    December 3, 2010 at 3:34 am | #1

    I usually never let comments on IT blogs but yours obviously deserves something :)

    Thanks for the tips i’am looking for that since months ….

  2. test
    December 16, 2010 at 11:05 pm | #2

    Thank you SOOOOOOOOOOOOOOOOOOOOOOOOOO Much!!! You’re blog was EXTREMELY useful. Thank you again. It was a saviour! :)

  3. Pete Keavney
    March 3, 2011 at 8:40 am | #3

    Being new to MySQL and somewhat annoyed at it’s lack of certain db functions I had become accustomed to taking for granted.. this little tidbit saved me a considerable amount of time. Thank you of this ingenious solution to a vexing problem.

  4. smile1
    March 12, 2011 at 12:06 pm | #4

    Thank you very much! I was searching a long time for a solution like this. :)

  5. Sood
    April 17, 2011 at 3:14 pm | #5

    Hats off…Just let me know one thing…can the conditions be applied to this rownum field…If yes,How??

  6. LJ
    September 25, 2011 at 1:57 am | #6

    Brilliant solution! thanks so much!!

  7. Andy
    January 19, 2012 at 5:43 pm | #7

    Thanks Kevin this indeed saved a lot of time. I had got to the point where I had realised the sql was not processing my query in the correct order. I created a view which contained the joins and required order by statements and then performed the select query on the view with the rownum variable in an attempt to resolve but it still processed the rows in the wrong order. Your method works a treat – cheers.

  8. Alaa
    June 16, 2012 at 11:13 pm | #8

    thank you :)

  9. Rajaa
    July 15, 2012 at 7:02 am | #9

    greeting from jordan :) ,you are good man to help us and share that .. regards

  1. November 1, 2010 at 3:33 am | #1
  2. January 13, 2013 at 1:17 pm | #2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: