> > Is this intentional?
> Yes this behaviour is inteded. All storage engines are handled like this.
> The problem is that the federated engine can actually only be used for
> small tables. Currently I have a system set up that went very slow because
> the I used queries on larger Federated table. If you look how the MySQL
> core isses select statements from more complex joins you will see that the
> number of Selects on the federated system is extremly high. I have had a
> join issuing 250000 on the federated database just to resolve the one
> join. One worst case scenario is that the federated engine selects the
> federated database multible times for one row returned to the user.
> This has been giving me quite some trouble. Also the show table status as
> an initialisation for every query gives me some problems. There seems to
> be some sort of race condition in MySQL 4 Innodb that creates a deadlock
> if issue enough Show Table status. I've been wanting to submit a bugreport
> on that but have not put enough of blood into tracking the bug down.
Can you please give an example of a query that will cause so many SELECTs to
be issued against the remote host? Is this issue limited to the federated
table handler -- in other words, will a MyISAM handler be called a million
times under such circumstances? If so, how can this query run with any
reasonable speed - is it because the MyISAM caches allow it to respond to
those millions of requests substantially faster than a FEDERATED engine