CODEWITHSUNDEEP
sqlleft-join
King Bosman
King Bosman

Reputation: 17

left join same table twice slow query

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

So I have 2 left joins in my query which select from same table but with a where on one. Since its the same table I select from the query is super slow. How can I fix this?

Upvotes: 0

Views: 1127

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

First, no need to use the order by clause in derived tables.
Second, your first derived table simply selects all the records in the table, so I don't see the point of it.

Try this:

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

Or this:

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

Upvotes: 1

Related Questions