#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    13
    Rep Power
    0

    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
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    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

IMN logo majestic logo threadwatch logo seochat tools logo