October 22nd, 2013, 01:19 AM
-
Optimizing Query
We have a white label system running over 700 sites, and I need to optimize queries. Our server techs said it would be helpful if we minimize internal temporary tables when using order by and group by.
The query below has an order by and a group by, but ones in a subquery. I am not sure if this is creating a internal temporary table, or if anyone can help optimize this query:
Code:
SELECT
items.*
FROM
items
LEFT JOIN
(
SELECT
item_id,
COUNT(*) AS ordered_cnt
FROM
ordered_items
GROUP BY
item_id
) AS ordered
ON
ordered.item_id = items.itemid
INNER JOIN
item_properties AS cat_ips
ON
cat_ips.item_id = items.itemid
WHERE
items.multiplesof = ?
AND
items.stock = ?
AND
cat_ips.property_id = ?
ORDER BY
ordered.ordered_cnt DESC
Any help is much appreciated.
My resources:
http://dev.mysql.com/doc/refman/5.0/en/internal-temporary-tables.html
Thanks
-Billy
October 22nd, 2013, 03:55 AM
-
you can easily determine if an internal temporary table is required by doing an EXPLAIN (as mentioned in the manual)
however, you cannot get away from it if you want the results ordered by the number of ordered items
change the LEFT JOIN to INNER JOIN
October 23rd, 2013, 06:09 PM
-
As Rudy says, a LEFT JOIN against a derived table like that will always create a temporary table.
The thing is that when you use a LEFT JOIN the join order will always be from left to right, and if you have as in your query a derived table that has to be searched in for row in the left table it's a lot of searches in that derived table. OTOH if this derived table is very small then this is not a problem. So it's all about how large this temporary table is.
Using an INNER JOIN as Rudy suggests will give your Database the option to choose join order and possibly start with derived table and then use the index on items.itemid (if you have created one).
But then again sometimes you need a LEFT JOIN and then you sometimes will have to rethink your query.
/Stefan