In the first post in this series I talked about the design process for my new banner system. I wanted it to be 100% accurate so I eliminated any sort of random number generation process. I also eliminated a SELECT … FOR UPDATE because I was concerned about deadlocks affecting the efficiency of my code. At the end of the post I introduced the MySQL LAST_INSERT_ID() function. Today I will cover it in much more detail.
MySQL offers an interesting function called LAST_INSERT_ID() that – once I figured out the proper syntax – provided exactly what I needed for my accurate and efficient banner system. What does it do? Simply put, it provides a shortcut to return the result of a previous SQL statement without having to worry about intervening updates. Before I explain the function, I want to share a bit of the database design and how the process will eventually work.
Page Count Math
I have a table called phpbb_views. The structure of this table is very simple: it only contains a single column named view_ctr. The field is currently defined as
int(11) unsigned which means it can go up to a really large number. What can I say… I’m optimistic. I have had code in place for years that increments this value on every page view. Previously I had the update statement running inside the includes/page_tail.php code. For the banner system I moved that logic into the includes/page_header.php code instead.
Let me assume that I have 10 banners that are all equally weighted. I want to ensure that these banners are displayed in sequential order, from one to ten and then starting over again. There is a perfect function for this, and you might have seen it if you’ve looked at how phpBB2 generates alternating row colors for tables. The syntax looks like this:
($var1 % $var2)
The result is the remainder of $var1 divided by $var2. Note that both $var1 and $var2 are converted to integer values (if needed) prior to the operation. How can this help me?
A sequential number (like page view counter values) will increment by one, starting at some number, and extending to infinity. What I need is a rotating sequence of numbers that starts at 1 and goes to 10 and then starts over again. That’s exactly what the % operator will do for me. All I have to do is use my page view counter as $var1 and 10 (the number of banners) as $var2 and here’s what the numbers look like:
+-----+--------------+ | 100 | 0 | | 101 | 1 | | 102 | 2 | | 103 | 3 | | 104 | 4 | | 105 | 5 | | 106 | 6 | | 107 | 7 | | 108 | 8 | | 109 | 9 | | 110 | 0 | +-----+--------------+
If you notice the column on the left has an increasing sequence, while the column on the right starts with zero and increments up to nine and then starts over again. This is because 100 / 10 has no remainder, while 101 / 10 has a remainder of one, and so on. This is perfect! Now all I have to do is ensure that I will never skip a number and I have a guaranteed way to pick the next (and correct) banner to display. If I have a zero-based array index these numbers are already perfect. If my array index starts at one then all I have to do is change the formula to
($var1 % $var2) + 1 and I am all set.
How the MySQL LAST_INSERT_ID() Function Works
I will jump right to the syntax of my query and then explain it next:
UPDATE phpbb_views SET view_ctr = LAST_INSERT_ID(view_ctr+1) ;
This statement accomplishes two things at the same time. First, it updates the view_ctr column using the provided expression (view_ctr + 1). Second, the LAST_INSERT_ID() function makes a note of the resulting value and stores it as a part of my session. In a sense, it is doing the update and the select all at the same time! This eliminates any potential issue with statements appearing out of order. It also eliminates any need to perform any locking on the rows or the table.
To retrieve the session value I issue this command:
SELECT LAST_INSERT_ID() ;
That is simple, isn’t it? As per the MySQL documentation this query does not even reference a table. It pulls the last affected value from the session variable and provides it to me. If the view_ctr value was 110 and I run the first command the new value for view_ctr is 111 and that value is also cached for my session. When I retrieve the value using the second query I get 111.
All of my problems are solved. I can use this technique without worrying about a deadlock and without worrying about missing values. It didn’t take long to set up my code with this function. But is it fast?
On average that query takes about 0.00015 seconds. That’s not too bad. What’s even better is that the query to set the new value as well as the one required to update the banner counters both are equally as efficient.
My final banner system provides me with the following features:
- Banners can have start dates or expiration dates
- Advertisers can have multiple banners on file
- Advertisers can “weight” banners so they appear more (or less) frequently
- Page views are evenly distributed across all active banners according to weight
There are many more features in place, but the last bullet point is the result of the work done to get this far. I will talk more about the table design and how I completed my banner system in my next post.