Q: How to optimize my mysql query to retrieve data faster I have a standalone table, we insert it's data through a weekly job, and retrieve data in our search module.
the table has around 4 millions records (and will get bigger) when I execute the following select query it take long time (around 15 second). I am using MySql DB.
SELECT 
    v.venueId,
    (SELECT 
            MIN(totalPerPerson)
        FROM
            PricingArtifact pa
        WHERE
            pa.venueId = v.venueId
                AND pa.mealId IN (SELECT
                    m.mealId
                FROM
                    Meal m,
                    MealDay md
                WHERE
                    m.mealId = md.mealId
                        AND m.mealDeletedAt IS NULL
                        AND md.mealDayDeletedAt IS NULL
                        AND m.venueId = v.venueId)) AS minPrice
FROM
    Venue v

Please note that 


*

*venue table has only 12 records.

*I have index for venueId, totalPerPerson in pricingArtifact table.

*I tried join instead of subquery, and it's giving the same result.

*meal and meal day table they have less than 100 record each.


A: IF you are only looking for the venue and minimum total per person, you can do that directly from the pricingArtifact down without the actual Venue as the pricingArtifact HAS the venueID... unless you want the venue description data too.
By means of transitive property, if the V.VenueID = PA.VenueID, then the PA.VenueID can be joined to the M.VenueID without use of the venue table.  Similarly, the PA.MealID can be directly joined to the meal and MealDay tables.
Now, you also mention the table of 4 million records.  Your correlated query (querying the min() value perPerson/perVenue is very pricey as you already encounter as it does the sub query for every record from the first table.  Do you really need to go back through all historical records, or is there some DATE field, such as you only care about activity over the last... say 30 days?  Once the data is finished, does it ever really change?  Maybe keeping a summary table as basis to analyze pricing rates.
Now, for indexes. I would suggest the following.
table            index
PricingArtifact  (venueID, mealID, totalPerPerson )
Meal             (mealID, MealDeletedAt )
MealDay          (mealID, MealDayDeletedAt )


SELECT
      PA.venueID
      MIN( PA.TotalPerPerson ) as VenueMinPerPerson
   from
      PricingArtifact PA
         JOIN meal M
             ON PA.MealID = M.MealID
            AND M.MealDeletedAt IS NULL
         JOIN MealDay MD
             ON PA.MealID = MD.MealID
            AND MD.MealDayDeletedAt Is NULL


A: I am thinking you don't need the join to venue at all, if there really are only 12 of them.
SELECT pa.venueID, MIN(PA.TotalPerPerson) as minPrice
FROM PricingArtifact pa
WHERE EXISTS (SELECT 1
              FROM Meal m JOIN
                   MealDay md
                   ON m.mealId = md.mealId
              WHERE m.mealDeletedAt IS NULL AND
                    md.mealDayDeletedAt IS NULL AND
                    pa.mealId = m.mealId
             )
GROUP BY pa.venueId;

For this query, you want indexes on Meal(mealId, mealDeletedAt) and MealDay(mealId, mealDayDeletedAt).
There are some curiosities about your query:


*

*I normally would not describe the expression MIN(totalPerPerson) as "MinTotalPrice".

*The join on Meal, MailDay, and PriceArtifact are all on the same column.  That seems strange.

