How can I optimize a query that returns a lot of records, then order by a date field and return only the latest one? : <p>I am not so into database and I have the following doubt about the possible optimization of this query (defined on a <strong>MySql</strong> database):</p>

<pre><code>SELECT MCPS.id AS series_id,
        MD_CD.market_details_id AS market_id,
        MD_CD.commodity_details_id AS commodity_id,
        MD.market_name AS market_name,
        MCPS.price_date AS price_date,
        MCPS.avg_price AS avg_price,
        CU.ISO_4217_cod AS currency, 
        MU.unit_name AS measure_unit, 
        CD.commodity_name_en,
        CN.commodity_name 
FROM Market_Commodity_Price_Series AS MCPS
INNER JOIN MeasureUnit AS MU ON MCPS.measure_unit_id = MU.id
INNER JOIN Currency AS CU ON MCPS.currency_id = CU.id
INNER JOIN MarketDetails_CommodityDetails AS MD_CD ON MCPS.market_commodity_details_id = MD_CD.id
INNER JOIN MarketDetails AS MD ON MD_CD.market_details_id = MD.id
INNER JOIN CommodityDetails AS CD ON MD_CD.commodity_details_id = CD.id
INNER JOIN CommodityName AS CN ON CD.id = CN.commodity_details_id
INNER JOIN Languages AS LN ON CN.language_id  = LN.id
WHERE MD.id = 4
AND CD.id = 4 
AND LN.id=1
ORDER BY price_date DESC LIMIT 1
</code></pre>

<p>It contains a lot of join only because the information in the tables are very normalized.</p>

<p>This query returns the information related the last price of a commodity (<strong>AND CD.id = 4</strong>) in a specific market (**WHERE MD.id = 4).</p>

<p>So I am first retrieving the list of all prices of a specific commodity in a specific market (that can be a lot of records) and then I do:</p>

<pre><code>ORDER BY price_date DESC LIMIT 1
</code></pre>

<p>I think that maybe this way can be computationally expensive because the first part of the query (the entire query except the last <strong>ORDER BY</strong> statment will retrieve a lot of records that then have to be ordered.</p>

<p>Is it a problem? Eventually what could be a smart strategy to optimize this query? (I have no idea...)</p>
