JOIN query optimization using indexes-CodePudding

The question is: How to increase the speed of this query?

SELECT
    c.CategoryName,
    sc.SubcategoryName,
    pm.ProductModel,
    COUNT(p.ProductID) AS ModelCount
FROM Marketing.ProductModel pm
    JOIN Marketing.Product p
        ON p.ProductModelID = pm.ProductModelID
    JOIN Marketing.Subcategory sc
        ON sc.SubcategoryID = p.SubcategoryID
    JOIN Marketing.Category c
        ON c.CategoryID = sc.CategoryID
GROUP BY c.CategoryName,
    sc.SubcategoryName,
    pm.ProductModel
HAVING COUNT(p.ProductID) > 1

Schema:

I tried creating some indexes and reorganizing the order of the JOINs. This did not increase productivity in the least. Maybe I need other indexes or a different query?
My solution:

CREATE INDEX idx_Marketing_Subcategory_IDandName ON Marketing.Subcategory (CategoryID)
CREATE INDEX idx_Marketing_Product_PMID ON Marketing.Product (ProductModelID)
CREATE INDEX idx_Marketing_Product_SCID ON Marketing.Product (SubcategoryID)
        
        SELECT
            c.CategoryName,
            sc.SubcategoryName,
            pm.ProductModel,
            COUNT(p.ProductID) AS ModelCount
        FROM Marketing.Category AS c
            JOIN Marketing.Subcategory AS SC
                ON c.CategoryID = SC.CategoryID
            JOIN Marketing.Product AS P
                ON SC.SubcategoryID = p.SubcategoryID
            JOIN Marketing.ProductModel AS PM
                ON P.ProductModelID = PM.ProductModelID
        GROUP BY c.CategoryName,
            sc.SubcategoryName,
            pm.ProductModel
        HAVING COUNT(p.ProductID) > 1

UPD:
Results:

Plan with my indexes:
Plan

CodePudding user response：

Your query has a cost of 0.12 which is trivial, as is the number of rows, it executes in microseconds, row esitmates are also reasonably close so it's not clear what the problem is you are trying to solve.

Looking at the execution plan there is a key lookup for ProductModelId with an estimated cost of 44% of the query, so you could eliminate this with a covering index by including the column in the index Product.idx_Marketing_Product_SCID

Create index idx_Marketing_Product_SCID on Marketing.Product (SubcategoryID)
include (ProductModelId) with(drop_existing=on)