Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
Cardinality for non-unique index will not updated, until one row was inserted and
analyze table was executed.
Create Statement.
CREATE TABLE TAB1 ( |
CAMPO1 INT NOT NULL AUTO_INCREMENT, |
CAMPO2 INT NOT NULL DEFAULT '0', |
INDEX IND_TAB1 (CAMPO2), |
PRIMARY KEY (CAMPO1) |
) ENGINE=Aria;
|
SHOW INDEX FROM TAB1; |
show cardinality for PK = 0 and for Non-unique = NULL. |
MariaDB [test]> show index from TAB1;
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| TAB1 | 0 | PRIMARY | 1 | CAMPO1 | A | 0 | NULL | NULL | | BTREE | | | |
| TAB1 | 1 | IND_TAB1 | 1 | CAMPO2 | A | NULL | NULL | NULL | | BTREE | | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
I would expect cardinality a not NULL like for Index CAMP01.
An Analyze does not help;
ANALYZE TABLE TAB1;
MariaDB [test]> ANALYZE TABLE TAB1; |
+-----------+---------+----------+-----------------------------+ |
| Table | Op | Msg_type | Msg_text | |
+-----------+---------+----------+-----------------------------+ |
| test.TAB1 | analyze | status | Table is already up to date | |
+-----------+---------+----------+-----------------------------+ |
Message "Table is already up to to date" is wrong.
CAMP02 cardinality is mssing.
Output of show index from TAB1; is still the same.
MariaDB [test]> show index from TAB1;
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| TAB1 | 0 | PRIMARY | 1 | CAMPO1 | A | 0 | NULL | NULL | | BTREE | | | |
| TAB1 | 1 | IND_TAB1 | 1 | CAMPO2 | A | NULL | NULL | NULL | | BTREE | | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
If I insert two values.
INSERT INTO TAB1 (CAMPO1) VALUES (1);
|
INSERT INTO TAB1 (CAMPO1) VALUES (2);
|
MariaDB [test]> show index from TAB1;
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| TAB1 | 0 | PRIMARY | 1 | CAMPO1 | A | 2 | NULL | NULL | | BTREE | | | |
| TAB1 | 1 | IND_TAB1 | 1 | CAMPO2 | A | NULL | NULL | NULL | | BTREE | | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
The cardinality of the PK CAMP01 was updated, the cardinality of CAMP02 is still NULL.
The Expected cardinality updated value for CAMP02 instead NULL.
Cardinality CAMP01 was updated, CAMP02 not.
Now a analyze table can help.
MariaDB [test]> analyze table TAB1;
|
+-----------+---------+----------+----------+e
|
| Table | Op | Msg_type | Msg_text |
|
+-----------+---------+----------+----------+
|
| test.TAB1 | analyze | status | OK |
|
+-----------+---------+----------+----------+
|
 |
MariaDB [test]> show index FROM TAB1;
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| TAB1 | 0 | PRIMARY | 1 | CAMPO1 | A | 2 | NULL | NULL | | BTREE | | | |
| TAB1 | 1 | IND_TAB1 | 1 | CAMPO2 | A | 1 | NULL | NULL | | BTREE | | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
Now cardinality is as expected.
Adding an additional value.
MariaDB [test]> INSERT INTO TAB1 (CAMPO1,CAMPO2) VALUES (3,4); |
Query OK, 1 row affected (0.00 sec)
|
 |
MariaDB [test]> show index FROM TAB1; |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
| TAB1 | 0 | PRIMARY | 1 | CAMPO1 | A | 3 | NULL | NULL | | BTREE | | | |
| TAB1 | 1 | IND_TAB1 | 1 | CAMPO2 | A | 3 | NULL | NULL | | BTREE | | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ |
2 rows in set (0.00 sec) |
|
Now cardinality will be updated as expected. Both indices cardinality was updated.
Expected behaviour would be, that cardinality from non-unique index was updated in the same way like unique keys.
Richard