Optimize this SQL query?

Question

I am using MySQL DB. I have two tables -

1. Product_Data (it has some columns including PK productDataId)
2. Product_Data_Link(it has 5 columns Id(PK), productDataId(FK), LinkTypeId, IsActive, ProductDataLinkUrl)

I am running this query in one service call for multiple products. Is there any way to optimize it for fast execution.

select
    link1.ProductDataLinkUrl as ProductDataUrl1,
    link2.ProductDataLinkUrl as ProductDataUrl2,
    link3.ProductDataLinkUrl as ProductDataUrl3 ,
    link4.ProductDataLinkUrl as ProductDataUrl4 
FROM
    product_data pd 
left join
    product_data_links link1 
        on link1.ProductDataId = pd.ProductDataId 
        and link1.ProductDataLinkTypeId = 1 
        and link1.ProductDataLinkIsActive = 1 
left join
    product_data_links link2 
        on link2.ProductDataId = pd.ProductDataId 
        and link2.ProductDataLinkTypeId = 2 
        and link2.ProductDataLinkIsActive = 1 
left join
    product_data_links link3 
        on link3.ProductDataId = pd.ProductDataId 
        and link3.ProductDataLinkTypeId = 3 
        and link3.ProductDataLinkIsActive = 1 
left join
    product_data_links link4 
        on link4.ProductDataId = pd.ProductDataId 
        and link4.ProductDataLinkTypeId = 4 
        and link4.ProductDataLinkIsActive = 1 
WHERE
    pd.ProductDataId = 99999

Answer 1

Your query should be fine with the following indexes:

• product_data(ProductDataId)
• product_data_links(ProductDataId, ProductDataLinkTypeId, ProductDataLinkIsActive)

Under some circumstances, it can be faster to use group by:

  select max(case when ProductDataLinkTypeId = 1 then ProductDataLinkUrl end) as ProductDataUrl1,
         max(case when ProductDataLinkTypeId = 2 then ProductDataLinkUrl end) as ProductDataUrl2,
         max(case when ProductDataLinkTypeId = 3 then ProductDataLinkUrl end) as ProductDataUrl3,
         max(case when ProductDataLinkTypeId = 4 then ProductDataLinkUrl end) as ProductDataUrl4
  from product_data_links pdl
  where ProductDataId = 99999 and
        ProductDataLinkIsActive = 1 and
        ProductDataLinkTypeId in (1, 2, 3, 4);

You want the second index above for this query.