MySQL Bugs: #26697: Every query to a federated table results in a full scan of MyISAM table.

Bug #26697	Every query to a federated table results in a full scan of MyISAM table.
Submitted:	27 Feb 2007 21:53	Modified:	15 Mar 2007 16:25
Reporter:	Jose Luis Palacios	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Federated storage engine	Severity:	S2 (Serious)
Version:	5.0.38-BK, 5.0.27, 5.1	OS:	Linux (Linux, Windows 2000)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I have two servers: linux1 with MySQL V5.0.27 (compiled with support for FEDERATED engine) and linux2 with MySQL V5.0.18.
I have a federated table in linux1 that points to a MyISAM table in linux2. This last table contains about 2000000 registers.
When I trying to do any select query in federated table, it is too slow and I watch that in linux2 server is wrote as slow query a full scan of the table. Always a full scan! although I done:
select count(*) from <table>; or
select <field1> from table where <conditions>;
Always wrote as slow query a full scan and, furthermore, linux2 sends the 2000000 registers result of full scan query to linux1 through the LAN.
With this behaviour, a “select count(*) from <table>” costs locally 0.11 seconds but with federated table it costs 34 seconds!

I have also tried with MySQL V5.1 in a Windows 2K server as federated engine and MySQL V5.0.27 in Linux with MyISAM table and it happens the same.

The mysqld.log shows that always a full scan table is done and sent to federated engine.

How to repeat:
Create a federate table and a MyISAM table in two distinct servers.
Enable log file of MyISAM server.
Every query on federate table results in a full scan of MyISAM table.

Thank you for a problem report. Please, try to repeat with a newerr version, 5.0.27, on linux2, and inform about the results. Please, send als SHOW CREATE TABLE results for both MyISAM and FEDERATED table. Send exact queries you execute, with EXPLAIN results for them.

Please, take a look at likely related bugs, bug #25571 and bug #25572 also. I am sure that not every query to every federated table leads to full table scan...

show create table + three queries

Attachment: 26697-1.doc (application/msword, text), 60.00 KiB.

I have attached 26697-1.doc.

Thank's for your quick support!

I verifed the behaviour described with latest 5.0.38-BK on Linux using the following simple test case (4.0.30 was started with general query log enabled):

openxs@suse:~/dbs/4.0> bin/mysql --socket=/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.0.30-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
Database changed this feature to get a quicker startup with -A
mysql> drop table t1;
Query OK, 0 rows affected (0.03 sec)

mysql> create table t1(c1 int primary key, c2 char(100));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1(c1) values (1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> explain select count(*) from t1\G
*************************** 1. row ***************************
Comment: Select tables optimized away
1 row in set (0.00 sec)

So, for original (MyISAM) table there is no need to access table at all, if you use indexed column!

mysql> explain select count(c1) from t1\G
*************************** 1. row ***************************
Comment: Select tables optimized away
1 row in set (0.00 sec)

mysql> explain select count(c2) from t1\G
*************************** 1. row ***************************
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra:
1 row in set (0.00 sec)

Now, let's create FEDERATED table:

openxs@suse:~/dbs/4.0> cd ../5.0
openxs@suse:~/dbs/5.0> bin/mysql -uroot --host=127.0.0.1 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.38 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table tf;
ERROR 1051 (42S02): Unknown table 'tf'
mysql> create table tf(c1 int primary key, c2 char(100)) engine=federated conne
ction='mysql://root:root@127.0.0.1:3340/test/t1';
Query OK, 0 rows affected (0.01 sec)

mysql> show create table tf;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------+
| Table | Create Table

                                            |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------+
| tf    | CREATE TABLE `tf` (
  `c1` int(11) NOT NULL,
  `c2` char(100) default NULL,
  PRIMARY KEY  (`c1`)
) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://root:root@127.0.0.
1:3340/test/t1' |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select count(*) from tf;
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------+
|  1 | SIMPLE      | tf    | ALL  | NULL          | NULL | NULL    | NULL |  105
 |       |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------+
1 row in set (0.00 sec)

mysql> select count(*) from tf;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.01 sec)

mysql> exit
Bye

Now let us check general query log of target instance:

openxs@suse:~/dbs/5.0> cat ../4.0/var/suse.log
/home/openxs/dbs/4.0/libexec/mysqld, Version: 4.0.30-log, started with:
Tcp port: 3340  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
...
070312 11:54:16       3 Query       drop table t1
070312 11:54:24       3 Query       create table t1(c1 int primary key, c2 char(
100))
070312 11:54:33       3 Query       insert into t1(c1) values (1), (2), (3), (4)
, (5)
070312 11:54:38       3 Query       explain select count(*) from t1
070312 11:54:42       3 Query       explain select count(c1) from t1
070312 11:54:46       3 Query       explain select count(c2) from t1
070312 11:55:09       3 Quit
...
070312 11:56:06       7 Connect     root@localhost on test
                      7 Query       SELECT *  FROM `t1` WHERE  1=0
                      7 Quit
070312 11:56:09       8 Connect     root@localhost on test
070312 11:56:16       8 Query       SHOW TABLE STATUS LIKE 't1'
070312 11:56:25       8 Query       SHOW TABLE STATUS LIKE 't1'
                      8 Query       SELECT `c1`, `c2` FROM `t1`

So, we obviously select ALL COLUMNS (?) and ALL ROWS just to get count(*). This is a bug, that seriosly limits FEDERATED tables usage in practice.

Yes, it is the same issue, thanks.
I am just a little disappointed that this issue is not solved from 2007 till now.
I was hoping to have something similar with DB Links from other systems also in MySQL and Federated was the only workaround for this.

I see now why this engine is off by default :) is not very useful in real life.