October 22nd, 2013, 01:19 AM
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:
Any help is much appreciated.
COUNT(*) AS ordered_cnt
) AS ordered
ordered.item_id = items.itemid
item_properties AS cat_ips
cat_ips.item_id = items.itemid
items.multiplesof = ?
items.stock = ?
cat_ips.property_id = ?
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.