MySQL: Break query after one row found-CodePudding

I have a two tables:

wallets
movements

where the relationship is wallet.id = movements.wallet_id

I want to check if any wallet has done a certain type of movement

What I do is:

SELECT w.*,
       CASE WHEN (
           SELECT COUNT(m.id)
           FROM movements m
           WHERE m.wallet_id = w.id  
           AND m.type = 'BUY'
        )>0 THEN TRUE ELSE FALSE END AS has_buy_movement,
       CASE WHEN (
           SELECT COUNT(m.id)
           FROM movements m
           WHERE m.wallet_id = w.id  
           AND m.type = 'SELL'
        )>0 THEN TRUE ELSE FALSE END AS has_sell_movement
FROM wallet w

In this way I get what I want but my problem is that my query is very "heavy" to do because movements might be thousands. So to me could be ok to break it after one row is found.

If I put the LIMIT 1 at the end of the subquery it doesn't really change because in the COUNT I still get the total number.

Is there an alternative way?

Thank you

CodePudding user response：

Use EXISTS which returns as soon as a match is found:

SELECT w.*,
       EXISTS (SELECT 1 FROM movements m WHERE m.wallet_id = w.id AND m.type = 'BUY') AS has_buy_movement,
       EXISTS (SELECT 1 FROM movements m WHERE m.wallet_id = w.id AND m.type = 'SELL') AS has_sell_movement
FROM wallet w;