Support » Plugins » Hacks » Left join slows down mysql query

  • Resolved Chris


    Hi everyone.

    I am using an ecommerce plugin with my WordPress installation to sell products. By default this plugin orders my product post type just like WordPress orders the default post type–newest to oldest.

    In my online catalog I have products that are in stock, out of stock (which can be re-ordered), and sold (which cannot be re-ordered). I need to order these products like so: in stock > out of stock > sold. To do this, I have created a meta key called “product_stock” whose value is numeric–3 for in stock, 2 for out of stock, 1 for sold. I order them by this value using the following two functions:

    // Join for ordering products and search pages by product quantity
    add_filter('posts_join', 'meta_join');
    function meta_join($join) {
    	if ( is_store_page() || is_search() ) :
    		global $wpdb;
    		//join stock
    		return $join . " LEFT JOIN (
    		SELECT *
    		FROM $wpdb->postmeta
    		WHERE meta_key = 'product_stock') AS stock
    		ON $wpdb->posts.ID = stock.post_id";
    	return $join;
    //Orderby for ordering products and search pages by quantity
    add_filter('posts_orderby', 'product_meta_orderby');
    function product_meta_orderby($orderby) {
    	if ( is_store_page() || is_search() ) :
    		global $wpdb;
    		return " wp_posts.post_type DESC, stock.meta_value DESC, wp_posts.post_date DESC ";
    	return $orderby;

    (As a side note, the is_store_page() function is one that I have created to detect whether my users are looking at one of my store pages or at a normal page or blog page.)

    So basically I am wondering if there might be a faster way to implement the ordering that I need, because currently queries on my store pages run very, very slowly.

Viewing 2 replies - 1 through 2 (of 2 total)
  • Rather than the inner SELECT why not,

        $wpdb->postmeta stock
        $wpdb->posts.ID = stock.post_id AND
        stock.meta_key = 'product_stock'




    Of course! That does it, thank you very much. 🙂

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Left join slows down mysql query’ is closed to new replies.