Posts Tagged ‘Stored procedure’

Manage hierarchical data with MySQL stored procedures

Monday, May 21st, 2012

Below you will find all code to create the table and the stored procedures to manage hierarchical trees in MySQL.
The following stored procedures are provided:

  • tree_add_root()
  • tree_add_node(id,name,label,description)
  • tree_update_node(id,label,description)
  • tree_get_all(depth,indentstring)
  • tree_get_branch(id,depth,indentstring)
  • tree_get_branch_by_name(parentname,name,depth,indentstring)
  • tree_get_parents(id,indentstring)
  • tree_swap_leafs(id1,id2)
  • tree_del(id)

All code is documented and can be downloaded in a zip file.
Note that I do not describe the parameters for each procedure in this article. The parameters are described in the code.
Also, the procedures try to verify the input of the stored procedures and appropriate error messages will be returned if needed.

The method used to store a tree in the database:
Almost all applications need to store and manage hierarchical data in a database.
There are several ways of doing this. The ‘adjacency list model’ or the ‘recursion method’ is the most commonly used model.
A table created for this method will look something like this:

CREATE TABLE categories (
id       INT NOT NULL  PRIMARY KEY
, name     VARCHAR(50) NOT NULL
, parent_id INT NULL
, FOREIGN KEY parent_id_fk (parent_id)
REFERENCES categories (id)
);

This is the most used method because it is very simple to understand the relations by looking at the data, very simple to insert the data and relative simple to retrieve it.
But retrieving the data can be slow and inefficient. You will need multiple joins or recursive functions in your application to retrieve the results you want.

Another way to store trees in your database is the Modified Preorder Tree Traversal method.
A table created for this method will look something like this:

CREATE TABLE categories(
id     INT NOT NULL PRIMARY KEY
,name   VARCHAR(50) NOT NULL
,lft    INT NOT NULL
,rht    INT NOT NULL
);

In this model there is no parent_id but the left and right columns define the relations. See this article by Joe Celko for an explanation.

With this model you can retrieve the whole tree as simple as this:
SELECT * FROM categories ORDER BY lft;

To retrieve a branch in the tree:
SELECT * FROM categories WHERE lft BETWEEN 1 AND 20 ORDER BY lft;

So, it is very simple and much more efficient to retrieve the data with this method.

How to set it up?
Install the table:

mysql>source create_tables.sql

This will create the table ‘trees’ if it does not already exists.

Install the stored procedures:

mysql>source create_procedures.sql

This will create the procedures as described above. Note that they will overwrite existing stored procedures if they have the same name. Be careful!

Set up test data:
mysql>source populate_trees.sql

How to use the stored procedures
To test if everything works run the procedure to show the whole tree and have the branches indented by ‘–’:

mysql>call tree_get_all(NULL,'--');
+---------+-----------+---------------------+------------------+-----+-----------+
| tree_id | name      | label               | description      | lvl | is_branch |
+---------+-----------+---------------------+------------------+-----+-----------+
|       1 | Root      | Root                | NULL             |   0 |         1 |
|       2 | pages     | --Pages             | Pages in website |   1 |         1 |
|       4 | Home      | ----Home            | NULL             |   2 |         1 |
|       5 | About     | ------About us      | NULL             |   3 |         1 |
|       6 | Contact   | --------Contact us  | NULL             |   4 |         0 |
|       7 | Events    | --------Our events  | NULL             |   4 |         0 |
|       8 | Service   | ------Service       | NULL             |   3 |         1 |
|       9 | Products  | --------Products    | NULL             |   4 |         1 |
|      10 | Search    | ----------Search    | NULL             |   5 |         0 |
|      11 | Reviews   | ----------Reviews   | NULL             |   5 |         0 |
|      25 | Furniture | ----------Furniture | NULL             |   5 |         0 |
|      12 | FAQ       | --------FAQ         | NULL             |   4 |         0 |
|      13 | Order     | ------Shoppingcart  | NULL             |   3 |         0 |
|       3 | products  | --Products          | Product list     |   1 |         1 |
|      14 | Furniture | ----Furniture       | NULL             |   2 |         1 |
|      16 | Living    | ------Living        | NULL             |   3 |         1 |
|      19 | Chairs    | --------Chairs      | NULL             |   4 |         0 |
|      20 | Tables    | --------Tables      | NULL             |   4 |         0 |
|      17 | Kitchen   | ------Kitchen       | NULL             |   3 |         0 |
|      18 | Bedroom   | ------Bedroom       | NULL             |   3 |         1 |
|      21 | Chairs    | --------Chairs      | NULL             |   4 |         0 |
|      22 | Tables    | --------Tables      | NULL             |   4 |         0 |
|      23 | Beds      | --------Beds        | NULL             |   4 |         0 |
|      24 | Closets   | --------Closets     | NULL             |   4 |         0 |
|      15 | Office    | ----Office suplies  | NULL             |   2 |         0 |
+---------+-----------+---------------------+------------------+-----+-----------+
25 rows in set (0.00 sec)

The example data has two trees, one for the navigation in our website and another tree for products that we sell.
tree_id : The id of the node, this is the primary key
name : name of the node, must be unique (case sensitive) in a branch
label : the pretty name of the node
description : an optional long description of the node
lvl : the level of the node in the tree
is_branch : if it is ‘1′ there is a sub-branch available. If it is ‘0′ then this node is a leaf in the tree.

Now lets get the first two levels in the tree indented by 2 spaces:

mysql>call tree_get_all(2,'  ');
+---------+-----------+--------------------+------------------+-----+-----------+
| tree_id | name      | label              | description      | lvl | is_branch |
+---------+-----------+--------------------+------------------+-----+-----------+
|       1 | Root      | Root               | NULL             |   0 |         1 |
|       2 | pages     |   Pages            | Pages in website |   1 |         1 |
|       4 | Home      |     Home           | NULL             |   2 |         1 |
|       3 | products  |   Products         | Product list     |   1 |         1 |
|      14 | Furniture |     Furniture      | NULL             |   2 |         1 |
|      15 | Office    |     Office suplies | NULL             |   2 |         0 |
+---------+-----------+--------------------+------------------+-----+-----------+
6 rows in set (0.00 sec)

Get only a branch of the tree:
Get the ‘Products’ branch and only it’s direct children and do not indent it:

mysql>call tree_get_branch(3,1,NULL);
+---------+-----------+----------------+--------------+-----+-----------+
| tree_id | name      | label          | description  | lvl | is_branch |
+---------+-----------+----------------+--------------+-----+-----------+
|       3 | products  | Products       | Product list |   1 |         1 |
|      14 | Furniture | Furniture      | NULL         |   2 |         1 |
|      15 | Office    | Office suplies | NULL         |   2 |         0 |
+---------+-----------+----------------+--------------+-----+-----------+
3 rows in set (0.00 sec)

Or get a branch by it’s name.
Because it is very likely that a tree has duplicate names in it, you need to provide the name of the parent and the name of the node that you want to retrieve. If this combination is used more then once in the tree, only one result is retrieved.
This procedure is not as efficient as the one above and in case of duplicate parent-child combinations the result can be unpredictable.
But it is much more verbose to ask for ‘Root’,'products’ then only for ‘id:3′.
Note that the names are case sensitive.
E.g.;

mysql>call tree_get_branch_by_name('Root','products',1,NULL);
+---------+-----------+----------------+--------------+-----+-----------+
| tree_id | name      | label          | description  | lvl | is_branch |
+---------+-----------+----------------+--------------+-----+-----------+
|       3 | products  | Products       | Product list |   1 |         1 |
|      14 | Furniture | Furniture      | NULL         |   2 |         1 |
|      15 | Office    | Office suplies | NULL         |   2 |         0 |
+---------+-----------+----------------+--------------+-----+-----------+
3 rows in set (0.00 sec)

To retrieve the parents of the node ‘Beds’, for a breadcrumb for example, use;

mysql>call tree_get_parents(23,NULL);
+---------+-----------+-----------+--------------+-----+
| tree_id | name      | label     | description  | lvl |
+---------+-----------+-----------+--------------+-----+
|       1 | Root      | Root      | NULL         |   0 |
|       3 | products  | Products  | Product list |   1 |
|      14 | Furniture | Furniture | NULL         |   2 |
|      18 | Bedroom   | Bedroom   | NULL         |   3 |
+---------+-----------+-----------+--------------+-----+
4 rows in set (0.00 sec)

Adding, removing and changing the tree:

Add the root to the tree:
When you first set up the table you first need to add a root to the tree. You only need to do this once.
This will give you an error message if the table trees is not empty.
mysql>call tree_add_root();

Add a new node to the tree:

mysql>call tree_add_node(18,'beds',NULL,NULL);
+---------+
| tree_id |
+---------+
|      26 |
+---------+
1 row in set (0.01 sec)

This has added the node ‘beds’ to the parent ‘Bedroom’. The new tree_id for this node is returned.
We left the ‘label’ parameter empty so the name will be used as the label too.

Note that in the parent ‘Bedroom’ there is already another node called ‘Beds’.
We are allowed to add this node name because the names of the nodes are case sensitive. Another ‘Beds’ would generate an error message.
Let’s see the result:

mysql:call tree_get_branch(18,1,'  ');
+---------+---------+-----------+-------------+-----+-----------+
| tree_id | name    | label     | description | lvl | is_branch |
+---------+---------+-----------+-------------+-----+-----------+
|      18 | Bedroom | Bedroom   | NULL        |   3 |         1 |
|      21 | Chairs  |   Chairs  | NULL        |   4 |         0 |
|      22 | Tables  |   Tables  | NULL        |   4 |         0 |
|      23 | Beds    |   Beds    | NULL        |   4 |         0 |
|      24 | Closets |   Closets | NULL        |   4 |         0 |
|      26 | beds    |   beds    | NULL        |   4 |         0 |
+---------+---------+-----------+-------------+-----+-----------+
6 rows in set (0.00 sec)

Change a node:
We noticed that the label and description of the newly inserted node is not correct and we can change it like this:

mysql>call tree_update_node(26,'Beds','other beds');
Query OK, 1 row affected (0.04 sec)

mysql:call tree_get_branch(18,1,'  ');
+---------+---------+-----------+-------------+-----+-----------+
| tree_id | name    | label     | description | lvl | is_branch |
+---------+---------+-----------+-------------+-----+-----------+
|      18 | Bedroom | Bedroom   | NULL        |   3 |         1 |
|      21 | Chairs  |   Chairs  | NULL        |   4 |         0 |
|      22 | Tables  |   Tables  | NULL        |   4 |         0 |
|      23 | Beds    |   Beds    | NULL        |   4 |         0 |
|      24 | Closets |   Closets | NULL        |   4 |         0 |
|      26 | beds    |   Beds    | other beds  |   4 |         0 |
+---------+---------+-----------+-------------+-----+-----------+
6 rows in set (0.00 sec)

Change the order of leafs:
We can change the order of the leafs like this:

mysql>call tree_swap_leafs(24,26);
Query OK, 0 rows affected (0.04 sec)

mysql> call tree_get_branch(18,1,'  ');
+---------+---------+-----------+-------------+-----+-----------+
| tree_id | name    | label     | description | lvl | is_branch |
+---------+---------+-----------+-------------+-----+-----------+
|      18 | Bedroom | Bedroom   | NULL        |   3 |         1 |
|      21 | Chairs  |   Chairs  | NULL        |   4 |         0 |
|      22 | Tables  |   Tables  | NULL        |   4 |         0 |
|      23 | Beds    |   Beds    | NULL        |   4 |         0 |
|      26 | beds    |   Beds    | other beds  |   4 |         0 |
|      24 | Closets |   Closets | NULL        |   4 |         0 |
+---------+---------+-----------+-------------+-----+-----------+
6 rows in set (0.00 sec)

Remove a node:
And finally we can remove a node from the tree.
If the node has children, these children will be removed too.

mysql>call tree_del(18);
Query OK, 6 rows affected (0.01 sec)

mysql> call tree_get_branch(18,1,'  ');
ERROR 1644 (45000): The tree_id does not exists.

Let’s try it one level higher to see the whole furniture branch:

mysql>call tree_get_branch(14,NULL,'  ');
+---------+-----------+------------+-------------+-----+-----------+
| tree_id | name      | label      | description | lvl | is_branch |
+---------+-----------+------------+-------------+-----+-----------+
|      14 | Furniture | Furniture  | NULL        |   2 |         1 |
|      16 | Living    |   Living   | NULL        |   3 |         1 |
|      19 | Chairs    |     Chairs | NULL        |   4 |         0 |
|      20 | Tables    |     Tables | NULL        |   4 |         0 |
|      17 | Kitchen   |   Kitchen  | NULL        |   3 |         0 |
+---------+-----------+------------+-------------+-----+-----------+
5 rows in set (0.00 sec)

UPDATE
In response to the question from Max about counting the children of each node I added a new column to the output of the tree result.
The new column is called “cnt_children”.

mysql>call tree_get_all(NULL,'--');
+---------+-----------+---------------------+------------------+-----+--------------+-----------+
| tree_id | name      | label               | description      | lvl | cnt_children | is_branch |
+---------+-----------+---------------------+------------------+-----+--------------+-----------+
|       1 | Root      | Root                | NULL             |   0 | 24           |         1 |
|       2 | pages     | --Pages             | Pages in website |   1 | 11           |         1 |
|       4 | Home      | ----Home            | NULL             |   2 | 10           |         1 |
|       5 | About     | ------About us      | NULL             |   3 | 2            |         1 |
|       6 | Contact   | --------Contact us  | NULL             |   4 | 0            |         0 |
|       7 | Events    | --------Our events  | NULL             |   4 | 0            |         0 |
|       8 | Service   | ------Service       | NULL             |   3 | 5            |         1 |
|       9 | Products  | --------Products    | NULL             |   4 | 3            |         1 |
|      10 | Search    | ----------Search    | NULL             |   5 | 0            |         0 |
|      11 | Reviews   | ----------Reviews   | NULL             |   5 | 0            |         0 |
|      25 | Furniture | ----------Furniture | NULL             |   5 | 0            |         0 |
|      12 | FAQ       | --------FAQ         | NULL             |   4 | 0            |         0 |
|      13 | Order     | ------Shoppingcart  | NULL             |   3 | 0            |         0 |
|       3 | products  | --Products          | Product list     |   1 | 11           |         1 |
|      14 | Furniture | ----Furniture       | NULL             |   2 | 9            |         1 |
|      16 | Living    | ------Living        | NULL             |   3 | 2            |         1 |
|      19 | Chairs    | --------Chairs      | NULL             |   4 | 0            |         0 |
|      20 | Tables    | --------Tables      | NULL             |   4 | 0            |         0 |
|      17 | Kitchen   | ------Kitchen       | NULL             |   3 | 0            |         0 |
|      18 | Bedroom   | ------Bedroom       | NULL             |   3 | 4            |         1 |
|      21 | Chairs    | --------Chairs      | NULL             |   4 | 0            |         0 |
|      22 | Tables    | --------Tables      | NULL             |   4 | 0            |         0 |
|      23 | Beds      | --------Beds        | NULL             |   4 | 0            |         0 |
|      24 | Closets   | --------Closets     | NULL             |   4 | 0            |         0 |
|      15 | Office    | ----Office suplies  | NULL             |   2 | 0            |         0 |
+---------+-----------+---------------------+------------------+-----+--------------+-----------+
25 rows in set (0.00 sec)

Download the code
You can download the stored procedures in this zip file: trees_2012-11-26.zip

Use the strict sql mode when compiling a MySQL stored procedure to avoid unexpected errors.

Thursday, May 10th, 2012

Setting the mode to STRICT_ALL_TABLES when you compile your stored procedure or function can prevent a lot of subtle bugs in your MySQL application.
Consider this example:

DELIMITER //
CREATE FUNCTION test(p_first TINYINT, p_second TINYINT) RETURNS TINYINT
BEGIN
    DECLARE v_result TINYINT;
    SET v_result := p_first + p_second;
    RETURN v_result;
END//
DELIMITER ;

compile it and then test the funcion:

mysql> select test(126,1);
+-------------+
| test(126,1) |
+-------------+
|         127 |
+-------------+
1 row in set (0.00 sec)

mysql> select test(127,1);
+-------------+
| test(127,1) |
+-------------+
|         127 |
+-------------+
1 row in set, 1 warning (0.00 sec)

The first test produced the correct answer but the second test was incorrect. The reason is that the variable v_result is a TINYINT and this will not hold a value higher then 127.
The problem is that you will not get an error but an incorrect result!

It get’s even worse: you can assign a string to the TINYINT parameter:

mysql> select test('a',1);
+-------------+
| test('a',1) |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

The invalid value for the parameter is ignored and the function produces an incorrect result.

In an interactive prompt you will see a warning next to the result but this will get unnoticed if this function was called within your application. You can show the warning like this:

mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1264 | Out of range value for column 'v_result' at row 1 |
+---------+------+---------------------------------------------------+
1 row in set (0.00 sec)

You can prevent this problem by setting your sql mode to STRICT_ALL_TABLES when compiling the procedure or function. When executing the procedure in another sql mode you will still get the error because only the sql mode during compiling the procedure matters.

Let’s compile it again. We will save the current sql mode in a user variable, Set the sql mode to STRICT_ALL_TABLES, compile the function and restore the old sql mode.

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES';
DELIMITER //
CREATE FUNCTION test(p_first TINYINT, p_second TINYINT) RETURNS TINYINT
BEGIN
    DECLARE v_result TINYINT;
    SET v_result := p_first + p_second;
    RETURN v_result;
END//
DELIMITER ;
SET sql_mode=@OLD_SQL_MODE;

Now test it again:

mysql> select test(127,1);
ERROR 1264 (22003): Out of range value for column 'v_result' at row 1

So update your template for a procedure or function and add the code for saving the sql mode like this:

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES';

DELIMITER //
DROP FUNCTION IF EXISTS function_name;
//

CREATE FUNCTION function_name(
     p_param1 INT
    ,p_param2 VARCHAR(255)  
) RETURNS INT
/**
 * Description of function
 * <br>
 * %author Ronald Speelman
 * %version 1.0
 * %param p_param1                 Description of the parameter
 * %return result of calculation
*/

BEGIN
    /* declare variables */
    DECLARE v_result INT;

    /* do the real work here */
    SET v_result := NULL;

    /* this is your one and only exit point */
    RETURN v_result;
END//
DELIMITER ;

SET sql_mode=@OLD_SQL_MODE;

Avoid locks when storing counters in MySQL

Wednesday, May 9th, 2012

If your application has a need for storing counters you can use this package which contains the scripts for a table and some stored procedures to handle managing the counters.

A common problem with storing counters in a table is that every time your application update your counter, a row lock needs to be set on the row the table. This will be a problem when your application needs to do this a lot because your clients will be waiting for the locks to be released on the table and your application will come to a halt. When you are counting page hits or clicks on links on a busy website, you will have a serious chance this will be the bottleneck of your application.

This will happen if your table look something like this:

1
2
3
4
5
6
counters
--------------------------
counter_name  VARCHAR(50)
counter_value INT
...
-------------------------

For each counter there is one row only and your application is sending more requests to update this single row then your database can handle and you will have a big traffic jam causing problems for the rest of your application. One way to solve this is adding more rows for the same counter. Then you will have more rows to update the values and less chance of a row lock. This will not work on a MyISAM table because this engine will add a table lock so only one row in the table can be updated at the same time.

By adding more rows for the same counter you will need some functionality for managing the counter. In this package you will find all functionality for managing the counters:
- Creating a counter with xx rows.
- Increment a counter by picking a random row for the counter and update it by one.
- Resetting the counter with another value and/or start date.
- Deleting the counter.
- Getting the value of the counter.
- Exception handling for not-existing counter names, creating duplicate counters etc.

How to set this up?
The script ’setup.sql’ will execute two other scripts:
create_tables.sql
This will create a table called: ‘counters’
The script will fail if there is already a table in your schema called ‘counters’

The script ‘create_procedures.sql’ will create 4 stored procedures and one function:
All procedures and the function are fully documented.
Stored procedures: counter_setup, counter_increment, counter_reset, counter_delete
Function: counter_get
The script will drop existing procedures and function if they already exist in your schema so make sure they do not exist yet.

How to use it?
If the table and procedures are created, you can insert some test data:

mysql> call counter_setup('test1','test1',NULL,NULL);
mysql> call counter_setup('test2','test2',10,1000);
mysql> call counter_setup('test3','test3',1,NULL);
mysql> select * from counters;
+--------------+------------+-------------+---------------------+---------+
| counter_name | counter_id | description | start_date          | counter |
+--------------+------------+-------------+---------------------+---------+
| test1        |          1 | test1       | 2012-05-02 07:44:49 |       0 |
| test1        |          2 | test1       | 2012-05-02 07:44:49 |       0 |
| test1        |          3 | test1       | 2012-05-02 07:44:49 |       0 |
| test1        |          4 | test1       | 2012-05-02 07:44:49 |       0 |
| test1        |          5 | test1       | 2012-05-02 07:44:49 |       0 |
| test2        |          1 | test2       | 2012-05-02 07:45:07 |    1000 |
| test2        |          2 | test2       | 2012-05-02 07:45:08 |       0 |
| test2        |          3 | test2       | 2012-05-02 07:45:08 |       0 |
| test2        |          4 | test2       | 2012-05-02 07:45:08 |       0 |
| test2        |          5 | test2       | 2012-05-02 07:45:08 |       0 |
| test2        |          6 | test2       | 2012-05-02 07:45:08 |       0 |
| test2        |          7 | test2       | 2012-05-02 07:45:08 |       0 |
| test2        |          8 | test2       | 2012-05-02 07:45:08 |       0 |
| test2        |          9 | test2       | 2012-05-02 07:45:08 |       0 |
| test2        |         10 | test2       | 2012-05-02 07:45:08 |       0 |
| test3        |          1 | test3       | 2012-05-02 07:45:31 |       0 |
+--------------+------------+-------------+---------------------+---------+
16 rows in set (0.00 sec)

You will see that ‘test1′ is created with 5 rows and the initial value of the counter is 0. This is the default.
Counter ‘test2′ has 10 rows and has an initial value of 1000.
Counter ‘test3′ has only one row and the initial value of the counter is 0

Now increase a counter a couple of times:

mysql> call counter_increment('test2');
mysql> call counter_increment('test2');
mysql> call counter_increment('test2');
mysql> call counter_increment('test2');
mysql> call counter_increment('test2');

Get the new value for counter ‘test2′:

mysql> SELECT counter_get('test2') AS total;
+-------+
| total |
+-------+
|  1005 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM counters WHERE counter_name = 'test2';
+--------------+------------+-------------+---------------------+---------+
| counter_name | counter_id | description | start_date          | counter |
+--------------+------------+-------------+---------------------+---------+
| test2        |          1 | test2       | 2012-05-02 07:55:55 |    1000 |
| test2        |          2 | test2       | 2012-05-02 07:55:55 |       2 |
| test2        |          3 | test2       | 2012-05-02 07:55:55 |       0 |
| test2        |          4 | test2       | 2012-05-02 07:55:55 |       0 |
| test2        |          5 | test2       | 2012-05-02 07:55:55 |       1 |
| test2        |          6 | test2       | 2012-05-02 07:55:55 |       0 |
| test2        |          7 | test2       | 2012-05-02 07:55:55 |       0 |
| test2        |          8 | test2       | 2012-05-02 07:55:55 |       1 |
| test2        |          9 | test2       | 2012-05-02 07:55:55 |       0 |
| test2        |         10 | test2       | 2012-05-02 07:55:55 |       1 |
+--------------+------------+-------------+---------------------+---------+
10 rows in set (0.00 sec)

The rows are randomly updated

Reset the counter back to 1000 and update the start date to the current date:

mysql> call counter_reset('test2',1000,TRUE);
Query OK, 10 rows affected (0.00 sec)
mysql> SELECT * FROM counters WHERE counter_name = 'test2';
+--------------+------------+-------------+---------------------+---------+
| counter_name | counter_id | description | start_date          | counter |
+--------------+------------+-------------+---------------------+---------+
| test2        |          1 | test2       | 2012-05-02 08:02:53 |    1000 |
| test2        |          2 | test2       | 2012-05-02 08:02:53 |       0 |
| test2        |          3 | test2       | 2012-05-02 08:02:53 |       0 |
| test2        |          4 | test2       | 2012-05-02 08:02:53 |       0 |
| test2        |          5 | test2       | 2012-05-02 08:02:53 |       0 |
| test2        |          6 | test2       | 2012-05-02 08:02:53 |       0 |
| test2        |          7 | test2       | 2012-05-02 08:02:53 |       0 |
| test2        |          8 | test2       | 2012-05-02 08:02:53 |       0 |
| test2        |          9 | test2       | 2012-05-02 08:02:53 |       0 |
| test2        |         10 | test2       | 2012-05-02 08:02:53 |       0 |
+--------------+------------+-------------+---------------------+---------+
10 rows in set (0.00 sec)

Now delete the counter:
mysql> call counter_drop('test2');

How does it perform?
Let’s setup some counters.
This script (you can find it in the .zip file) will truncate the counters table, add 1500 counters with a random number of rows per counter (max 50). It will also analyze the table and show you a summary of the counters table. This script can take a little time since I did not used a bulk insert, sorry for that…

mysql> source test_insertdata.sql;
+---------------+-----------------------+
| Rows inserted | Avg. rows per counter |
+---------------+-----------------------+
|         38263 |                    25 |
+---------------+-----------------------+
1 row in set (0.01 sec)

Now update the counters table with 250 concurrent connections and increment 4 counters per user. We will do this a with 100 iterations with mysqlslap.
So this will make 100.000 updates to 4 counters only.

shell: mysqlslap --host=localhost --user=ronald -p --create-schema=schemas --concurrency=250 --iterations=100 --query="call counter_increment('countername100');call counter_increment('countername500');call counter_increment('countername1000');call counter_increment('countername1499')"
Enter password:
Benchmark
Average number of seconds to run all queries: 0.346 seconds
Minimum number of seconds to run all queries: 0.266 seconds
Maximum number of seconds to run all queries: 0.977 seconds
Number of clients running queries: 250
Average number of queries per client: 1

Let’s check if we indeed inserted 100.000 counts:

mysql>SELECT counter_get('counterName100') AS cnt100, counter_get('counterName500') AS cnt500, counter_get('counterName1000') AS cnt1000, counter_get('counterName1499') AS cnt1499;
+--------+--------+---------+---------+
| cnt100 | cnt500 | cnt1000 | cnt1499 |
+--------+--------+---------+---------+
|  25000 |  25000 |   25000 |   25000 |
+--------+--------+---------+---------+
1 row in set (0.01 sec)

This is how the rows for one counter look like:

mysql>SELECT * FROM counters WHERE counter_name = 'counterName100';
+----------------+------------+-----------------------+---------------------+---------+
| counter_name   | counter_id | description           | start_date          | counter |
+----------------+------------+-----------------------+---------------------+---------+
| counterName100 |          1 | counterDescription100 | 2012-05-09 17:31:55 |    1348 |
| counterName100 |          2 | counterDescription100 | 2012-05-09 17:31:55 |    1218 |
| counterName100 |          3 | counterDescription100 | 2012-05-09 17:31:55 |    1304 |
| counterName100 |          4 | counterDescription100 | 2012-05-09 17:31:55 |    1265 |
| counterName100 |          5 | counterDescription100 | 2012-05-09 17:31:55 |    1313 |
| counterName100 |          6 | counterDescription100 | 2012-05-09 17:31:55 |    1215 |
| counterName100 |          7 | counterDescription100 | 2012-05-09 17:31:55 |    1288 |
| counterName100 |          8 | counterDescription100 | 2012-05-09 17:31:55 |    1240 |
| counterName100 |          9 | counterDescription100 | 2012-05-09 17:31:55 |    1205 |
| counterName100 |         10 | counterDescription100 | 2012-05-09 17:31:55 |    1223 |
| counterName100 |         11 | counterDescription100 | 2012-05-09 17:31:56 |    1216 |
| counterName100 |         12 | counterDescription100 | 2012-05-09 17:31:56 |    1196 |
| counterName100 |         13 | counterDescription100 | 2012-05-09 17:31:56 |    1230 |
| counterName100 |         14 | counterDescription100 | 2012-05-09 17:31:56 |    1211 |
| counterName100 |         15 | counterDescription100 | 2012-05-09 17:31:56 |    1253 |
| counterName100 |         16 | counterDescription100 | 2012-05-09 17:31:56 |    1315 |
| counterName100 |         17 | counterDescription100 | 2012-05-09 17:31:56 |    1253 |
| counterName100 |         18 | counterDescription100 | 2012-05-09 17:31:56 |    1193 |
| counterName100 |         19 | counterDescription100 | 2012-05-09 17:31:56 |    1290 |
| counterName100 |         20 | counterDescription100 | 2012-05-09 17:31:56 |    1224 |
+----------------+------------+-----------------------+---------------------+---------+
20 rows in set (0.00 sec)

It turns out that counter100 was assigned 20 rows and the updates are done pretty evenly among the rows.

The performance is very good and we do not have any locks. However the performance can be improved.
When writing the counter_setup procedure it occurred to me that you do not always want to add the same amount of rows for all counters.
E.g. a counter for page hits on your homepage will need more rows then for example a counter for hits on your privacy policy page ;-)
In order to pick a random row for the counter we need to find out how many rows exist for the counter. If you always want to use a fixed amount of rows you can hard code it in the procedure and that will save you a lookup.

You can download all code, stored procedures, scripts for load testing and documentation in a zip file: counters_2012-05-09.zip