Slow SQL Optimization caused by Case:MySQL Federated Storage engine

I didn't encounter this case, but I use the federated storage engine in my production environment, so document it.

Before optimization

A SQL partially intercepts the content as follows, and it takes about 268 seconds to get the result:

After optimization

From the execution plan of this SQL, we can see that mego.trade_order does not appear in the table column. After viewing, mego.trade_order is a Federated storage engine. DBlink similar to Oracle exists locally in the form of a link, but the actual data file does not exist.

Therefore, the efficiency of the mego.trade_order table as a subquery is very poor, so it can be rewritten as join. Remove the subquery, let the mego.trade_order table be the first result join as far as possible, and reduce the number of times to fetch data from the remote server. The optimization is as follows. After optimization, it only takes 0.74 seconds.

Reference links for the Federated storage engine

Https://dev.mysql.com/doc/refman/5.5/en/federated-storage-engine.html