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.
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
source to share
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
, 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
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
could be dumber.
is the same as any other key
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
it cannot effectively do something simple like yours
source to share