Be wary of functions in SQL in Magento, such as MAX(). There may be a better way.

I just wrapped up a Healthcheck for a Magento ECG customer and I ran into an interesting issue.  IIRC, the problem wasn’t a client one but rather code that a community extension used.  I won’t mention which one.

This extension was generating a query that was looking for the maximum minimum price for all products in a category.  The resulting query was something like this.

SELECT MAX(min_price) FROM catalog_product_index_price AS cpip
INNER JOIN catalog_category_product_index AS ccpi
    ON ccpi.product_id = cpip.entity_id
WHERE ccpi.category_id = 2;

So, it was doing a lot of things right.  It was using the index tables, not the attribute tables.  It was also using indexes, so that was right too.  But take out the MAX() call and see what happens.  I’m running this on the Magento sample data set and it returns 1275 results.  And as we all know the sample data is not like most production systems.  The customer I was working with saw about 40k results on some queries and under load this was taking 20 seconds in some cases, though never less than 200ms.  The reason is because it’s essentially doing a table scan on the results to find the record with the highest value.

So how do we make this faster?

It’s super easy, and fun to do.

SELECT min_price FROM catalog_product_index_price AS cpip
INNER JOIN catalog_category_product_index AS ccpi
    ON ccpi.product_id = cpip.entity_id
WHERE ccpi.category_id = 2
ORDER BY min_price DESC
LIMIT 1;

Sorting by min_price and limiting by 1 gives you the same result with a much more efficient query.  On the client’s test system it went from a minimum of 200ms (possibly 10s of seconds) to >1ms.

If you are well versed in SQL this makes a lot of sense, and might have been what you did normally.  But for this community extension it isn’t what it did and I suspect that given that a lot of Magento developers are API-oriented this is what they tend to do.