I am very new to MySQL Indexing. I am trying to create Indexes for web_products table.
I have created Index IsDataActive_IsVerified_IsExpired_Price_Sale to optimize the below query;
SELECT
MAX(Price_Sale) as maxColumn
FROM
`web_products` `t`
WHERE
IsDataActive = 1 AND IsVerified = 1
AND IsExpired = 0
and when I analyze the query using Explain I am getting the best possible result "Select tables optimized away".
I have another query which performs very poorly. The web_products table may contain millions of products. It takes several minutes to execute the below query;
SELECT
COUNT(P.BrandID)
FROM
web_products AS P
WHERE
P.OriginalItemID IS NULL
AND P.IsDataActive = 1
AND P.IsExpired = 0
AND P.IsVerified = 1;
BrandID is FK to brands table. Here is the Explain Analysis for the above query.
How to optimize the above query?
Update 1
The Index mentioned in the answer is charmingly working for the above queries. I am able to get the results in less than 0.200 ms.
I have modified the second query and added MerchantID to the index now it takes 1 second to execute the below query. The Explain shows the number of rows examined is 88331. Is there any way to bring the execution time to milliseconds?
SELECT
P.BrandID, COUNT(P.BrandID) as Count, B.Name
FROM
web_products AS P
inner join
web_brands as B ON P.BrandID = B.ID
INNER JOIN
web_merchants M ON P.MerchantID = M.ID
LEFT JOIN
web_products_features F ON P.ID = F.Product_ID
WHERE
P.OriginalItemID IS NULL
AND P.IsDataActive = 1
AND P.IsExpired = 0
AND P.IsVerified = 1
AND B.IsDataActive = 1
group by P.BrandID
order by Count desc
Limit 100
