Zählen Sie die Anzahl der Laravel- und MySQL-bezogenen Modelle über Zwischentabellen unter Verwendung zusätzlicher Bedingungen.

Question

Ich habe eine MySQL-Abfrage (erstellt mit Laravel Eloquents Eager Loading und der withCount-Funktion), die bei der Verarbeitung großer Datenmengen einige Leistungsprobleme aufweist. Gibt es eine Möglichkeit, die folgende Abfrage zu verbessern?

Ich muss alle Geschäfte abrufen und die Anzahl der mit dem Geschäft verbundenen Produkte zählen (über eine Zwischentabelle verknüpft), aber es gibt eine zusätzliche Bedingung, dass die type_id des Geschäfts gleich ist type_id des Produkts. Ich denke, dass diese zweite Bedingung dazu führt, dass die Abfrage nicht den richtigen Index verwendet.

Zwischen den beiden Modellen gibt es eine Zwischentabelle.

Store(ID, Type_ID, Owner_ID) Store Product(Shop_ID, Product_ID)

Ich habe auch Indizes für alle Fremdschlüssel ein zusammengesetzter Index für shop_product(shop_id, product_id).

Meine Anfrage lautet also wie folgt:

select
    Geschäfte.*,
    (
    wählen
        zählen(*)
    aus
        Produkte
    Inner Join shop_products auf
        products.id = shop_products.product_id
    Wo
        shop.id = shop_products.shop_id
        und products.type_id = shop.type_id)
aus
    Geschäfte
Wo
    shop.owner_id in (?)

ist es möglich, dass diese Abfrage irgendwie optimiert werden könnte, vielleicht nicht mit der withCount whereColumn-Abfrage dieses Laravel?

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

Die vollständige Abfrage sieht folgendermaßen aus:

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

Muss ich einen kombinierten Index für store(id, type_id) und product(id, type_id) hinzufügen?

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'关系中了。还可以检查优化拉取商店的方式。