左联接同一表两次慢速查询

Question

LEFT JOIN (SELECT xx_coinType AS yy_type, xx_sell AS sell_xx
           FROM xxs
           ORDER BY xx_id DESC) AS xxs
               ON xxs.yy_type = zz.zz_coin
LEFT JOIN (SELECT xx_coinType AS yy_type, xx_sell AS old_sell_xx
           FROM xxs
           WHERE xx_time < UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 24 HOUR))
           ORDER BY xx_id DESC) AS old_xxs
               ON old_xxs.coin_type = zz.zz_coin

因此，我的查询中还有两个联接，它们从相同的表中选择，但在一个表上有一个where。因为我从查询中选择的表是超级慢的。我怎么才能解决这个问题？

Answer 1

首先，不需要在派生表中使用order by子句。

其次，您的第一个派生表只需选择表中的所有记录，所以我看不出其中的意义。

试试这个：

LEFT JOIN xxs
               ON xxs.yy_type = zz.zz_coin
LEFT JOIN (SELECT xx_coinType AS yy_type, xx_sell AS old_sell_xx
           FROM xxs
           WHERE xx_time < UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 24 HOUR))
           ) AS old_xxs
               ON old_xxs.coin_type = zz.zz_coin

或者这个：

LEFT JOIN (SELECT xx_coinType AS yy_type, xx_sell AS sell_xx
           FROM xxs
           WHERE xx_time >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 24 
           ) AS xxs
               ON xxs.yy_type = zz.zz_coin
LEFT JOIN (SELECT xx_coinType AS yy_type, xx_sell AS old_sell_xx
           FROM xxs
           WHERE xx_time < UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 24 HOUR))
           ) AS old_xxs
               ON old_xxs.coin_type = zz.zz_coin