Count the number of Laravel and Mysql related models through intermediate tables using additional conditions.

Question

I have a MySQL query (built using Laravel Eloquent's eager loading and the withCount function) that is having some performance issues when dealing with large data sets, is there any way to improve the query below?

I need to get all the stores and count the number of products related to the store (associated through an intermediate table), but there is an additional condition that the type_id of the store is equal to the type_id of the product. I think this second condition is causing the query not to use the correct index.

There is an intermediate table between the two models.

store(id, type_id, owner_id) product(id, type_id) store product(shop_id, product_id)

I have indexes on all foreign keys, also There is a composite index on shop_product(shop_id, product_id).

So my query is this:

select
    shops.*,
    (
    select
        count(*)
    from
        products
    inner join shop_products on
        products.id = shop_products.product_id
    where
        shops.id = shop_products.shop_id
        and products.type_id = shops.type_id)
from
    shops
where
    shops.owner_id in (?)

is it possible that this query could be optimized somehow, maybe not using this laravel's withCount whereColumn query?

... Shop::withCount(['products' => fn($query) => $query->whereColumn('products. type_id', '=', 'shops.type_id')]);

The complete query is like this:

Shop::whereIn('owner_id', [123])
            ->withCount(['products' => fn($query) => $query->whereColumn('products.type_id', '=', 'shops.type_id')])
            ->get()

Do I need to add a combined index on store(id, type_id) and product(id, type_id)?

Answer 1

I haven't tested this but I will try something similar

Shop::whereIn('owner_id', [123])
            ->withCount(['products' => fn($query) => $query->select(['id','type_id'])->whereColumn('products.type_id', '=', 'shops.type_id')])
            ->get()

So I just added some fields (the ones you need and the ones the app needs to identify the product), but if only the count is needed, I'd try without the ID.

I assume when you get "products" it will pull all the data and if there are "text" type fields like body/description etc. it will be slow.

Also, not sure, but you could try using type_id instead of products.type_id since you're already in the products relationship. Also check out optimizing the way you pull your store.