Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

HUGE_DICK_10_INCHES's avatar

Very slow query

I am dummy with sql.

$data = $model1->model2()->has('model3')->withCount('model3')->orderBy('name')->get();

If model 3 has up to 2M rows, indexes in id's. Model1 and model2 pivot with indexes od id's. Why does it take up to 5000ms to query it? Is there a better approach? Thanks

select
  `model2`.*,
  (
    select
      count(*)
    from
      `model3`
    where
      `model2`.`id` = `model3`.`model2_id`
      and `enabled` = 1
  ) as `model3_count`,
  `model1_model2`.`model1_id` as `pivot_model1_id`,
  `model1_model2`.`model2_id` as `pivot_model2_id`
from
  `model2`
  inner join `model1_model2` on `model2`.`id` = `model1_model2`.`model2_id`
where
  `model1_model2`.`model1_id` = 430
  and exists (
    select
      *
    from
      `model3`
    where
      `model2`.`id` = `model3`.`model2_id`
      and `enabled` = 1
  )
order by
  `name` asc
0 likes
3 replies
Tray2's avatar

I'm guessing that isn't your real query, but it's impossible to say why it takes time, my guess is that you don't have the proper indexes, and there is also the fact that at least one of the tables have 2M rows.

I suggest two things

  1. Change your username, nobody here cares about your imaginary genitals.
  2. Take your query and prepend it with the work EXPLAIN, that will give you some clues as what to do.
  3. Never share pseudo code like that, it makes it way harder to help you.
2 likes
HUGE_DICK_10_INCHES's avatar

@Tray2 This is query I use I just changed table names with model1 model2 and model3. Let me try to ask you something different for the same thing. How can I get count of relation data after whereHas or has? Since in my case it is returning count from all, not just relation ones..

Please or to participate in this conversation.