Improving whereHas performance

[omaresmael]

TL;DR

WhereHas subquery should select 1 (constant), instead of selecting all columns

where exists (select * from `posts` where `users`.`id` = `posts`.`user_id` and `title` = ?) #not very good query 
where exists (select 1 from `posts` where `users`.`id` = `posts`.`user_id` and `title` = ?) # good query

---

Hey everyone,
I was working with whereHas and noticed an opportunity to optimize its performance,
let's take this eloquent example

Author::query()
  ->select('id', 'name')
  ->whereHas('posts', function ($query) {
      $query->where('title', 'Awesome post');
  })->toSql()

this eloquent returns

select `id`, `name` from `users` where exists (select * from `posts` where `users`.`id` = `posts`.`user_id` and `title` = ?)

that means the database engine may have to fetch all columns of any matching rows, even though only the existence of the rows is important. This can result in unnecessary overhead and slower query performance, especially for large tables or complex queries.

we can optimize the query by selecting 1 instead of * in the sub-query, which will achieve the same goal (check if the row exists or not) without fetching all row's data

I have refactored the logic behind the subquery to get the optimized query and tested it locally

select `id`, `name` from `users` where exists (select 1 from `posts` where `users`.`id` = `posts`.`user_id` and `title` = ?)

would you like me to create a pr for it, or is that how intentionally you wanted the subquery to be?

please let me know, Thanks!

[systemsolutionweb]

looks great to me

[Angel5a]

MySQL EXISTS states:

Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.

Perhaps it can make sense in some other drivers, but I don't see any reason for DB to miss such an optimization.

[macropay-solutions]

Thank you for asking this.
I remember another question who exposed the down side (long execution time) of exists when additional conditions are added into the sub select. We improved the performance of such queries using in instead of exists. See this comment (DEMO_ONLY_sql_debugger) #49577 (reply in thread)