0

Environment information:
Windows 11
10.6.8-MariaDB

In SO https://stackoverflow.com/questions/20055644/mysql-counter-within-group I asked how can I add a counter within a group. It works quite well until I found a conflict when primary key and index are defined on the same columns.

Here is the example with real data:

SELECT *
FROM table0
ORDER BY id, date


+--------+------------+
|   id   |   date     |
+--------+------------+
| 120792 | 2010-03-22 |
| 120792 | 2010-10-23 |
| 120792 | 2010-11-12 |
...
+--------+------------+

Here are the size informations:

SELECT COUNT(*), COUNT(DISTINCT id), COUNT(DISTINCT date)
FROM table0
;

+----------+--------------------+-----------------------+
| COUNT(*) | COUNT(DISTINCT id) | COUNT(DISTINCT date)  |
+----------+--------------------+-----------------------+
|    50835 |               4394 |                  3666 |
+----------+--------------------+-----------------------+

... and here are three variations of index I tested:

-- Case (1): 
ALTER TABLE table0 ADD PRIMARY KEY(id,date);
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
|  Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| table0    |          0 | PRIMARY  |            1 | id          | A         |       10208 | \N       | \N     |      | BTREE      |         |               | NO      |
| table0    |          0 | PRIMARY  |            2 | date        | A         |       51040 | \N       | \N     |      | BTREE      |         |               | NO      |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

-- Case (2):
ALTER TABLE table0 ADD INDEX(id);
ALTER TABLE table0 ADD INDEX(date);
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
|  Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| table0    |          1 | id       |            1 | id          | A         |       10200 | \N       | \N     |      | BTREE      |         |               | NO      |
| table0    |          1 | date     |            1 | date        | A         |        7285 | \N       | \N     |      | BTREE      |         |               | NO      |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

-- Case (3):
ALTER TABLE table0 ADD PRIMARY KEY(id,date);
ALTER TABLE table0 ADD INDEX(id);
ALTER TABLE table0 ADD INDEX(date);
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
|  Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| table0    |          0 | PRIMARY  |            1 | id          | A         |       10208 | \N       | \N     |      | BTREE      |         |               | NO      |
| table0    |          0 | PRIMARY  |            2 | date        | A         |       51040 | \N       | \N     |      | BTREE      |         |               | NO      |
| table0    |          1 | id       |            1 | id          | A         |       10208 | \N       | \N     |      | BTREE      |         |               | NO      |
| table0    |          1 | date     |            1 | date        | A         |        8506 | \N       | \N     |      | BTREE      |         |               | NO      |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

The query to add a counter within grouping column id is:

SELECT id
     , date
     , @prev_id
     , @counter := IF(id = @prev_id, @counter + 1,1) counter
     , @prev_id := id                                prev
FROM table0
   , (SELECT  @prev_id := NULL
            , @counter := 0
     ) init
ORDER BY id, date            
LIMIT 30            
;

I add the user defined variable @prev_id to see better what happens.

In cases (1) and (2) I get this result:

+--------+------------+----------+---------+--------+
|   id   |    date    | @prev_id | counter |  prev  |
+--------+------------+----------+---------+--------+
| 120792 | 2010-03-22 | \N       |       1 | 120792 |
| 120792 | 2010-10-23 | 120792   |       2 | 120792 |
| 120792 | 2010-11-12 | 120792   |       3 | 120792 |
| 120792 | 2011-10-11 | 120792   |       4 | 120792 |
....
| 120792 | 2019-11-20 | 120792   |      18 | 120792 |
| 120792 | 2020-11-18 | 120792   |      19 | 120792 |
| 120792 | 2022-01-24 | 120792   |      20 | 120792 |
+--------+------------+----------+---------+--------+
| 120793 | 2010-03-22 | 120792   |       1 | 120793 |  <- new id which reset counter to 1
| 120793 | 2010-03-26 | 120793   |       2 | 120793 |
| 120793 | 2010-04-07 | 120793   |       3 | 120793 |
....
| 120793 | 2011-09-14 | 120793   |       8 | 120793 |
| 120793 | 2011-10-11 | 120793   |       9 | 120793 |
| 120793 | 2011-12-27 | 120793   |      10 | 120793 |
+--------+------------+----------+---------+--------+

This is the result which I expected. But using the combined primary key and additionally index on both columns (case (3)) the output:

+--------+------------+----------+---------+--------+
|   id   |    date    | @prev_id | counter |  prev  |
+--------+------------+----------+---------+--------+
| 120792 | 2010-03-22 |   122244 |       1 | 120792 |
| 120792 | 2010-10-23 |   122811 |       1 | 120792 |  <- no addition of 1 since @prev_id is not previous id
| 120792 | 2010-11-12 |   122358 |       1 | 120792 |
| 120792 | 2011-10-11 |   123036 |       1 | 120792 |
....
| 120792 | 2019-11-20 |   654876 |       1 | 120792 |
| 120792 | 2020-11-18 |   910380 |       1 | 120792 |
| 120792 | 2022-01-24 |  1054884 |       1 | 120792 |
+--------+------------+----------+---------+--------+
| 120793 | 2010-03-22 |   120792 |       1 | 120793 |
| 120793 | 2010-03-26 |   122593 |       1 | 120793 |
| 120793 | 2010-04-07 |   122576 |       1 | 120793 |
....
| 120793 | 2011-09-14 |   123080 |       1 | 120793 |
| 120793 | 2011-10-11 |   120792 |       1 | 120793 |
| 120793 | 2011-12-27 |   122038 |       1 | 120793 |
+--------+------------+----------+---------+--------+

@prev_id in case (3) contains not the previous id.

I wanted to show this issue using a simple example with only some few rows but I could not reproduce this: also case (3) had the expected output as in case (1) and (2).

Any idea what happens?

Edit 1: Here is the SHOW CREATE TABLE:

CREATE TABLE `table0` (
  `id`         int(10) unsigned NOT NULL,
  `datum`      date NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Primary key and index are added later.

Improve this question
1
  • We need to see SHOW CREATE TABLE.
    – Rick James
    Mar 28 at 17:37

1 Answer 1

Reset to default
1

MySQL is deprecating the assigning of @variables in SELECTs.

MariaDB will probably follow suit at some point.

See http://mysql.rjweb.org/doc.php/myisam2innodb#index_issue_2_column_pk for workarounds.

Possible reference (From changelog or reference notes):

---- 2018-10-22 8.0.13 General Availability -- -- Important Change -----

Setting user variables in statements other than SET is now deprecated due to issues that included those listed here:

The order of evaluation for expressions involving user variables was undefined.

The default result type of a variable is based on its type at the beginning of the statement, which could have unintended effects when a variable holding a value of one type at the beginning of a statement was assigned a new value of a different type in the same statement.

HAVING, GROUP BY, and ORDER BY clauses, when referring to a variable that was assigned a value in the select expression list, did not work as expected because the expression was evaluated on the client and so it was possible for stale column values from a previous row to be used.

Syntax such as SELECT @var, @var:=@var+1 is still accepted in MySQL 8.0 for backward compatibility, but is subject to removal in a future release.

Improve this answer
1
  • Thanks for the hint of user defined variables but I couldn't find any documentation of depricating user defined variables in SELECT statements. Do you have a reference? Also I couldn't find information that innodb does not allow composite primary key. Your link concerns a special case (composite with autoincrement column).
    – giordano
    Apr 5 at 10:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.