Slow SELECT ... LIMIT without WHERE clause on the joined table

I got a problem with the merged table engine:

I created a federated table pointing to a reasonably large remote table (about 800,000 rows, row size 211 bytes, MyISAM).

When sending the following request:

SELECT * FROM TABLE LIMIT 0,30

      

the request takes only 9 seconds.

Attempt:

SELECT * FROM TABLE WHERE primaryKey = 1234

      

fast as usual (<0.001 s).

I tried using a federated table across multiple db servers, always the same result. Now my question is, did something happen behind a curtain that I don't know? Does Mysql get the whole index without a WHERE clause? Is some kind of internal sorting required?

Anyway, in my opinion the remote db server serving the data should handle this without any delay, right?

Mysql version: 5.5.31

+3


source to share


1 answer


FEDERATED

has many problems. It essentially asks the other machine to send one line at a time. It's well worth the cost of the round trip.

The optimizer is not very good, especially with FEDERATED

, when "pushing" operations to another server. That is, instead of handing over work that could be done by another server, it will request records and then do the work to initialize the server.

Beware of timings like (<0.001s). This usually means that the request cache is enabled and the request has not been completed, but rather picked from the QC. With the FEDERATED

QC can not be maintained properly, so either it is automatically turned off, or it must be turned off. (I do not know what.)

SELECT * FROM TABLE TABLE 0.30

This gives no index. It extracts strings from "data". I expect it to pick up 30 lines (whichever is the first 30 in .MYD) and then quit. But it FEDERATED

could be dumber.



In case MyISAM

, PRIMARY KEY

is the same as any other key UNIQUE

.

One way to get more information about what's going on:

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

      

I would expect to see one or two handlers around 30. But from 9 seconds it can say around 800000. If "800000" then it turns out that FEDERATED

it cannot effectively do something simple like yours LIMIT

.

+1


source







All Articles