Description:
I've created a table with ENGINE=FEDERATED, then looked at it through INFORMATION_SCHEMA and it displays an incorrect value for TABLE_ROWS.
How to repeat:
Create table:
USE southwind;
CREATE TABLE "clienti" (
"IDCliente" varchar(5) NOT NULL default '',
"NomeSocietà" varchar(40) default NULL,
"Contatto" varchar(30) default NULL,
"Posizione" varchar(30) default NULL,
"Indirizzo" varchar(60) default NULL,
"Città" varchar(15) default NULL,
"Zona" varchar(15) default NULL,
"CAP" varchar(10) default NULL,
"Paese" varchar(15) default NULL,
"Telefono" varchar(24) default NULL,
"Fax" varchar(24) default NULL,
PRIMARY KEY ("IDCliente"),
KEY "CLIENTI_CAP" ("CAP"),
KEY "CLIENTI_PAESE" ("Paese")
) ENGINE=InnoDB;
insert into it some records (mine had 91) then create table based on FEDERATED engine
USE test;
CREATE TABLE "clienti" (
"IDCliente" varchar(5) NOT NULL default '',
"NomeSocietà" varchar(40) default NULL,
"Contatto" varchar(30) default NULL,
"Posizione" varchar(30) default NULL,
"Indirizzo" varchar(60) default NULL,
"Città" varchar(15) default NULL,
"Zona" varchar(15) default NULL,
"CAP" varchar(10) default NULL,
"Paese" varchar(15) default NULL,
"Telefono" varchar(24) default NULL,
"Fax" varchar(24) default NULL,
PRIMARY KEY ("IDCliente")
)
ENGINE=FEDERATED
COMMENT='mysql://root:password@localhost:3306/federated/test_table';
Then check the INFORMATION_SCHEMA:
SELECT * FROM information_schema."TABLES" T where table_schema = 'test' and table_name = 'clienti'
and you'll see that the TABLE_ROWS value is 10000
Suggested fix:
Make ANALYZE TABLE able to update such a value (the query optimizer should benefit of this) or set the TABLE_ROWS value to NULL for those tables.