使用附加條件透過中間表計算Laravel和Mysql相關的模型的數量。

Question

我有一個MySQL查詢（使用Laravel Eloquent的急切載入和withCount函數建構），在處理大數據集時有一些效能問題，有沒有辦法改進下面的查詢？

我需要取得所有的商店，並計算與商店相關的產品數量（透過中間表關聯），但是還有一個附加條件，即商店的type_id等於產品的type_id。我認為這個第二個條件導致查詢沒有使用正確的索引。

兩個模型之間有一個中間表。

商店（id，type_id，owner_id）產品（id，type_id）商店產品（shop_id，product_id）

我在所有外鍵上都有索引，還在shop_product（shop_id，product_id）上有一個複合索引。

所以我的查詢是這樣的：

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')]);

完整的查詢是這樣的：

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

我是否需要在商店（id，type_id）和產品（id，type_id）上新增組合索引？

Answer 1

我沒有測試過這個，但我會嘗試類似的東西

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

所以我剛剛添加了一些欄位（你需要的欄位和應用程式需要識別產品的欄位），但如果只需要計數，我會嘗試不使用ID。

我假設當你取得「products」時，它會拉取所有數據，如果有像body/description等「text」類型字段，速度會很慢。

此外，不確定，但你可以嘗試使用'type_id'而不是'products.type_id'，因為你已經在'products'關係中了。還可以檢查優化拉取商店的方式。