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

Question

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?

Mysql version: 5.5.31

For the first query, MySQL fetches all 800 000 rows, then it takes 30 of them starting from 0th offset of the internal result pointer and discards the rest. Since it's MyISAM, it involves a lot of disk I/O. One way to "help" MySQL do this faster is by implementing a small cheat. You can help it by doing something like `SELECT * FROM table WHERE id < 1000 LIMIT 0, 30`. Now, as you can already see - I called this a cheat because you need to somehow help MySQL to reduce the data set it searches through - PK is always ideal candidate for that. — N.B., Apr 28 '15 at 16:58

score 1 · Answer 1 · answered Apr 29 '15 at 05:56

FEDERATED has lots of problems. It essentially asks the other machine to send one row at a time. This costs round trip overhead.

The optimizer is not very good, especially with FEDERATED, in "pushing down" operations to the other server. That is, rather than handing off work that could be done by the other server, it will ask for the records, then do the work on the server initiation the query.

Beware of timings like (< 0.001s). It usually means that the Query cache is turned on, and the query was not really executed, but rather fetched from the QC. With FEDERATED, the QC cannot be maintained correctly, so either it is automatically disabled, or you should disable it. (I don't know which.)

SELECT * FROM TABLE LIMIT 0,30

That does not fetch any index. It fetches rows from the 'data'. I would expect it to fetch 30 rows (whichever 30 are first in the .MYD) and then quit. But FEDERATED could be dumber than that.

In the case of MyISAM, the PRIMARY KEY is that same as any other UNIQUE key.

One way to get some more insight into what is going on:

FLUSH STATUS;
SELECT * FROM TABLE LIMIT 0,30;
SHOW SESSION STATUS LIKE 'Handler%';

I would expect to see one or two Handlers of about 30. But, from your '9 seconds', it might say about 800000. If "800000", then it would appear that FEDERATED cannot efficiently do something as simple as your LIMIT.

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

1 Answers1