Search code examples
phpmysqloptimizationrds

How to optimize the MySQL query using multiple-column indexes?

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
Solution

  • The best index for both queries is probably:

    web_products(IsDataActive, IsExpired, IsVerified, OriginalItemId, BrandId, Price_Sale)

    The order of the first three columns is unimportant. This should work for the first query, because it is a covering index. If it affect performance, then use two indexes and drop Price_Sale from this one.