Slow SELECT ... LIMIT without WHERE clause on federated table

MySQL & MariaDB Other MySQL® Questions
1

Hi,

I’ve got a problem concerning the federated table engine:

I created a federated table pointing to a reasonable large remote table (about 800.000 rows, row size 211 Bytes, MyISAM).

When sending the following query:

SELECT * FROM TABLE LIMIT 0,30

the query takes always 9 seconds to complete.

Trying:

SELECT * FROM TABLE WHERE primaryKey = 1234

is fast as usual (< 0.001s).

I tried tried the federated table on several db servers, always the same result. Now my question is: Does something happen behind the curtain I don’t know off? Does Mysql fetch the whole index without a WHERE clause? Is some internal sorting required?

Anyway, in my opinion the remote db server serving the data should handle this without any delay, shouldn’t it?

Server running the federated engine:
Mysql 5.1.22
Debian Linux 2.6.18-5-amd64

Server serving the data:
Mysql 4.0.24
Debian Linux 2.6.8-12-amd64-k8-smp

Thanks for any help!

2

Have you tried to use EXPLAIN

[B]Quote:[/B]
[URL]http://dev.mysql.com/doc/refman/5.0/en/explain.html[/URL]

or PROFILE

Quote:

[URL]http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html[/URL]

to see what the query is doing?

3

as expected:

EXPLAIN SELECT *FROM tableLIMIT 0 , 30

id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE table ALL NULL NULL NULL NULL 96

Mysql only needs to look at 96 rows to retrieve the result but the query takes 9 seconds to complete. Like I said, if I issue the query ON the server (not using the federated table in another db), the query completes in < 0.001s.