[teeheiman]

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:

	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

[r937]

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

[sr]

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.