Search code examples
mysqlsqlsubqueryinnodb

setting LIMIT on subquery when amount of returned rows doesnt matter

im using a framework ORM to run my queries ... here is a example of ORM query output ... it is a relational query 

  select * from `clients` where exists 
  (select * from `transactions` where `clients`.`id` = `transactions`.`client_id`  )
  order by `id` desc limit 20 offset 02

i was wondering if putting limitation on subquery has any benefits performance wise in this query since it doesn't matter how many rows it returns

like 

  (select * from `transactions` where `clients`.`id` = `transactions`.`client_id`  LIMIT 1 )

or performance in this scenario is not dependent on how many rows we select in subquery ?

Solution 
  • where exists 
(select * from `transactions` where `clients`.`id` = `transactions`.`client_id`  )

    The suquery is used as an argument for an EXISTS condition. In english, that would translate as : this customer has at least one transaction.

    When processing this type of condition, MySQL generally optimizes the process to just check that at least one record is returned by the subquery. Using LIMIT in this context is useless, your RDBMS knows better.