|
For me cardinality is populated.
With your test case (with the different table name in ALTER TABLE statements) it happens after analyze table:
...
|
MariaDB [test]> ALTER TABLE `jobListDevices` ENABLE KEYS;
|
ERROR 1146 (42S02): Table 'test.jobListDevices' doesn't exist
|
MariaDB [test]> show index from `bad_table`;
|
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| bad_table | 1 | idx1 | 1 | id1 | A | NULL | NULL | NULL | | BTREE | | |
|
| bad_table | 1 | idx1 | 2 | id2 | A | NULL | NULL | NULL | | BTREE | | |
|
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
2 rows in set (0.00 sec)
|
|
MariaDB [test]> analyze table `bad_table`;
|
+----------------+---------+----------+----------+
|
| Table | Op | Msg_type | Msg_text |
|
+----------------+---------+----------+----------+
|
| test.bad_table | analyze | status | OK |
|
+----------------+---------+----------+----------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> show index from `bad_table`;
|
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| bad_table | 1 | idx1 | 1 | id1 | A | 3 | NULL | NULL | | BTREE | | |
|
| bad_table | 1 | idx1 | 2 | id2 | A | 12 | NULL | NULL | | BTREE | | |
|
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
2 rows in set (0.00 sec)
|
If I fix the table name, it happens upon ALTER .. ENABLE KEYS:
MariaDB [test]> ALTER TABLE `bad_table` ENABLE KEYS;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> show index from `bad_table`;
|
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| bad_table | 1 | idx1 | 1 | id1 | A | 3 | NULL | NULL | | BTREE | | |
|
| bad_table | 1 | idx1 | 2 | id2 | A | 12 | NULL | NULL | | BTREE | | |
|
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
2 rows in set (0.00 sec)
|
Please provide the output of your last show index and your cnf file(s) our output of show variables. Thanks.
|
|
Oh, sorry, here is the corrected SQL to replicate the problem:
DROP TABLE IF EXISTS `bad_table`;
|
CREATE TABLE `bad_table` (
|
`id1` INT(10) UNSIGNED NOT NULL,
|
`id2` INT(10) UNSIGNED NOT NULL,
|
PRIMARY KEY (`id1`, `id2`),
|
INDEX `idx2` (`id2`)
|
)
|
ENGINE=MyISAM;
|
|
/*!40000 ALTER TABLE `bad_table` DISABLE KEYS */;
|
INSERT INTO `bad_table` VALUES
|
(1,1),
|
(1,2),
|
(1,3),
|
(1,4),
|
(2,1),
|
(2,2),
|
(2,3),
|
(2,4),
|
(3,1),
|
(3,2),
|
(3,3),
|
(3,4)
|
;
|
/*!40000 ALTER TABLE `bad_table` ENABLE KEYS */;
|
show index from `bad_table`;
|
analyze table `bad_table`;
|
show index from `bad_table`;
|
Output:
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
|
bad_table 0 PRIMARY 1 id1 A 4 NULL NULL BTREE
|
bad_table 0 PRIMARY 2 id2 A 12 NULL NULL BTREE
|
bad_table 1 idx2 1 id2 A NULL NULL NULL BTREE
|
Table Op Msg_type Msg_text
|
NATCMTS.bad_table analyze status OK
|
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
|
bad_table 0 PRIMARY 1 id1 A 3 NULL NULL BTREE
|
bad_table 0 PRIMARY 2 id2 A 12 NULL NULL BTREE
|
bad_table 1 idx2 1 id2 A 4 NULL NULL BTREE
|
|
|
Output of "show variables"
|
|
Thank you.
It's an upstream bug (or rather "not a bug") http://bugs.mysql.com/bug.php?id=54031. It was closed with the following comment:
1. disable and enable key commands have nothing to do with primary key cardinality counting, thus there are NULLs;
2. cardinality values in the b/c column of the multi-column index are cardinality values of key(a,b)/(a,b,c) respectively, and they are correct.
Thus, I'm also closing it as not a bug. Please comment with your reasoning if you disagree.
|
|
The explanation makes sense, since there is no way to disable primary index. At least that's what documentation says. But if you look at idx2 it is not a primary index. Nor is it a unique index. And it should not be part of the primary index either.
|
|
Sorry, when I used your test case, for me it was exactly the first part of the primary key that didn't get the cardinality value; I didn't notice it was different for you.
MariaDB [test]> /*!40000 ALTER TABLE `bad_table` ENABLE KEYS */;
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> show index from `bad_table`;
|
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| bad_table | 0 | PRIMARY | 1 | id1 | A | NULL | NULL | NULL | | BTREE | | |
|
| bad_table | 0 | PRIMARY | 2 | id2 | A | 12 | NULL | NULL | | BTREE | | |
|
| bad_table | 1 | idx2 | 1 | id2 | A | 4 | NULL | NULL | | BTREE | | |
|
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
3 rows in set (0.01 sec)
|
I'll give it another shot.
|
|
The culprit here is myisam_repair_threads=4 which you have among the settings. With it (or with any value greater than 1) I am getting the same result as you:
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| bad_table | 0 | PRIMARY | 1 | id1 | A | 4 | NULL | NULL | | BTREE | | |
|
| bad_table | 0 | PRIMARY | 2 | id2 | A | 12 | NULL | NULL | | BTREE | | |
|
| bad_table | 1 | idx2 | 1 | id2 | A | NULL | NULL | NULL | | BTREE | | |
|
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
With the default myisam_repair_threads=1, the cardinality is missing for the first part of the PK as expected:
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
|
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
| bad_table | 0 | PRIMARY | 1 | id1 | A | NULL | NULL | NULL | | BTREE | | |
|
| bad_table | 0 | PRIMARY | 2 | id2 | A | 12 | NULL | NULL | | BTREE | | |
|
| bad_table | 1 | idx2 | 1 | id2 | A | 4 | NULL | NULL | | BTREE | | |
|
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|
It is reproducible on all of 5.1, 5.5, 10.0, as well as MySQL 5.6.
Since it's an upstream bug, normally we would re-file it at bugs.mysql.com according to our standard procedure. Are you willing to do so?
I must say though that I don't believe it's going to be fixed upstream because a) non-critical MyISAM bugs are hardly ever fixed anymore, since the focus has been shifted to InnoDB, and b) according to http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_myisam_repair_threads, multi-threaded repair is still considered beta-quality.
How critical is this problem for you?
|
|
I am pretty familiar with this bug. Was hopping after so many years it will get fixed.
You can close this bug if you'd like.
|
|
As discussed, I will keep it open, hopefully we'll get to it some day.
I'm also keeping the tag 'upstream' for information, even though the upstream bug was not filed as I don't see much point in doing it.
|
|
I don't think it's a bug. ENABLE KEYS may update index cardinality, but doesn't have to.
Only ANALYZE TABLE is required to calculate the index cardinality, other commands sometimes can do it "for free" as a byproduct of whatever they were doing, but it is not guaranteed that they do it.
Like SELECT * FROM t1 can return rows in some specific order, but you cannot rely on it and should use ORDER BY instead.
|
variables.txt