Re: Performance of count(*) and aggregate functions

[Jose Luis Palacios]

I have had the same problem.
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 4000000 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 4000000 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 hope that I am doing something wrong or else federated tables are a really rubbishy.

Anybody has an idea about what I am doing wrong?