Description:
SHOW INDEX does not report cardinality properly for FEDERATED table. It is always NULL.
For MyISAM table we have:
mysql> CREATE TABLE `test` (
-> `id` int(11) NOT NULL auto_increment,
-> `j` int(11) NOT NULL,
-> PRIMARY KEY (`id`),
-> KEY `j` (`j`)
-> ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.23 sec)
mysql> insert into test values(null, 1);
Query OK, 1 row affected (0.20 sec)
mysql> insert into test values(null, 2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values(null, 3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(null, 4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(null, 5);
Query OK, 1 row affected (0.01 sec)
mysql> analyze table test;
+-----------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| test.test | analyze | status | OK |
+-----------+---------+----------+----------+
1 row in set (0.06 sec)
mysql> show index from test\G
*************************** 1. row ***************************
Table: test
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: test
Non_unique: 1
Key_name: j
Seq_in_index: 1
Column_name: j
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
2 rows in set (0.01 sec)
That is, Cardinality=5 for both indexes. But for the FEDERATED table that refers to it we have:
mysql> select * from testf;
+----+---+
| id | j |
+----+---+
| 9 | 1 |
| 10 | 2 |
| 11 | 3 |
| 12 | 4 |
| 13 | 5 |
+----+---+
5 rows in set (0.02 sec)
mysql> show create table testf\G
*************************** 1. row ***************************
Table: testf
Create Table: CREATE TABLE `testf` (
`id` int(11) NOT NULL auto_increment,
`j` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `j` (`j`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://root:root@192.168.
0.1:3307/test/test'
1 row in set (0.00 sec)
mysql> show index from testf\G
*************************** 1. row ***************************
Table: testf
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: NULL
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: REMOTE
Comment:
*************************** 2. row ***************************
Table: testf
Non_unique: 1
Key_name: j
Seq_in_index: 1
Column_name: j
Collation: NULL
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: REMOTE
Comment:
2 rows in set (0.01 sec)
mysql> select version();
+------------------+
| version() |
+------------------+
| 5.0.34-debug-log |
+------------------+
1 row in set (0.00 sec)
This is a bug, as even if it is a know/intended limitation, it is not described in the manual, http://dev.mysql.com/doc/refman/5.0/en/federated-limitations.html.
How to repeat:
See Bug #25571 for initial test case setup.
Suggested fix:
Get cardinality from remote server?
Description: SHOW INDEX does not report cardinality properly for FEDERATED table. It is always NULL. For MyISAM table we have: mysql> CREATE TABLE `test` ( -> `id` int(11) NOT NULL auto_increment, -> `j` int(11) NOT NULL, -> PRIMARY KEY (`id`), -> KEY `j` (`j`) -> ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.23 sec) mysql> insert into test values(null, 1); Query OK, 1 row affected (0.20 sec) mysql> insert into test values(null, 2); Query OK, 1 row affected (0.01 sec) mysql> insert into test values(null, 3); Query OK, 1 row affected (0.00 sec) mysql> insert into test values(null, 4); Query OK, 1 row affected (0.00 sec) mysql> insert into test values(null, 5); Query OK, 1 row affected (0.01 sec) mysql> analyze table test; +-----------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------+---------+----------+----------+ | test.test | analyze | status | OK | +-----------+---------+----------+----------+ 1 row in set (0.06 sec) mysql> show index from test\G *************************** 1. row *************************** Table: test Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 5 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: test Non_unique: 1 Key_name: j Seq_in_index: 1 Column_name: j Collation: A Cardinality: 5 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: 2 rows in set (0.01 sec) That is, Cardinality=5 for both indexes. But for the FEDERATED table that refers to it we have: mysql> select * from testf; +----+---+ | id | j | +----+---+ | 9 | 1 | | 10 | 2 | | 11 | 3 | | 12 | 4 | | 13 | 5 | +----+---+ 5 rows in set (0.02 sec) mysql> show create table testf\G *************************** 1. row *************************** Table: testf Create Table: CREATE TABLE `testf` ( `id` int(11) NOT NULL auto_increment, `j` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `j` (`j`) ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://root:root@192.168. 0.1:3307/test/test' 1 row in set (0.00 sec) mysql> show index from testf\G *************************** 1. row *************************** Table: testf Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: NULL Cardinality: NULL Sub_part: NULL Packed: NULL Null: Index_type: REMOTE Comment: *************************** 2. row *************************** Table: testf Non_unique: 1 Key_name: j Seq_in_index: 1 Column_name: j Collation: NULL Cardinality: NULL Sub_part: NULL Packed: NULL Null: Index_type: REMOTE Comment: 2 rows in set (0.01 sec) mysql> select version(); +------------------+ | version() | +------------------+ | 5.0.34-debug-log | +------------------+ 1 row in set (0.00 sec) This is a bug, as even if it is a know/intended limitation, it is not described in the manual, http://dev.mysql.com/doc/refman/5.0/en/federated-limitations.html. How to repeat: See Bug #25571 for initial test case setup. Suggested fix: Get cardinality from remote server?