Optimize Relationship Query

Hey there,

Any help on this would be greatly appreciated.

I have 3 tables.

1. inventory
2. product
3. inventory_product

They all have their associated models and the inventory and product table are related through a belongsToMany relationship through the inventory_product table. On this pivot table, there is an additional pivot column for quantity.

Thus, a product can exists with different quantities at different inventories.

I am using the inventory to query the products available in that inventory. My problem is that I am running into very slow queries...about 3 seconds are so, especially when adding filtering parameters. Below is my code. Any help on this would be awesome.

I have no created any DB indexes or foreign keys yet...was thinking of using that as a last resort.

Thanks

Inventory.php
public function productsAll()
    {
        return $this
            ->belongsToMany(NewProduct::class, 'inventory_product', 'inventory_id', 'product_id')
            ->withPivot('quantity');
    }

InventoryProductController.php
public function index(Request $request, Inventory $inventory)
    {
        $allProducts       = $inventory
            ->productsAll()
            ->whereIn('category_id', $categoryIds)
            ->with(['tags', 'inventories', 'incentives', 'reviews'])
            ->select('new_products.*', 'quantity')
            ->get();

       

        return new ProductCollection($allProducts);
    }