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
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
Change your username, nobody here cares about your imaginary genitals.
Take your query and prepend it with the work EXPLAIN, that will give you some clues as what to do.
Never share pseudo code like that, it makes it way harder to help you.
@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..