Bug #25572 SHOW INDEX does not report cardinality (always NULL) for FEDERATED table
Submitted: 12 Jan 2007 10:04
Reporter: Valeriy Kravchuk Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S2 (Serious)
Version:5.0.34-BK, 5.0.27 OS:Linux (Linux)
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[12 Jan 2007 10:04] Valeriy Kravchuk
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?
[14 Sep 2008 1:32] Antony Curtis
Federated does not implement storage engine method for analyze table so it is currently a no-operation.

I may look at enhancing this for FederatedX.