www.webdeveloper.com
Results 1 to 4 of 4

Thread: Inner Join Query Not Working Fast In wordpress databse

  1. #1
    Join Date
    Oct 2011
    Location
    Pakistan
    Posts
    16

    Inner Join Query Not Working Fast In wordpress databse

    Hello out there here i am having difficulty in fetching the Brands Per ctegory in the sidebar of my website

    Here is the description ..
    I am using the feeds and feed uploader to upload feeds and create brands and Product categories in wordpress
    The Problem is there is No link Within the Product_category and product_brand and i want too show specific Brands in the
    Side bar of a specific category noot the whole brands list which iis going to long down

    so i tried out these heavy queries to fetch the brands as per category

    but using the INNER JOIN made the databse too slow and the website keeploading without showing brands the query is working fiine the only thing i want to know is to speed up the queries for the Customers so they dont fade up

    here are the queries i am using

    PHP Code:
    $term get_term_by('slug',get_query_var('term'),'product_category');
                
                    
            
    $q5 "SELECT DISTINCT p1.ID
                    FROM
                        
    $wpdb->posts p1
                        INNER JOIN 
    $wpdb->term_relationships tr1 ON (p1.ID = tr1.object_id)
                        INNER JOIN 
    $wpdb->term_taxonomy tt1 ON (tr1.term_taxonomy_id = tt1.term_taxonomy_id)
                    WHERE tt1.term_id = '
    $term->term_id'
                    "
    ;
                
    $q2 "
                    SELECT tt2.term_id
                    FROM
                        
    $wpdb->posts p2
                        INNER JOIN 
    $wpdb->term_relationships tr2 ON (p2.ID = tr2.object_id)
                        INNER JOIN 
    $wpdb->term_taxonomy tt2 ON (tr2.term_taxonomy_id = tt2.term_taxonomy_id)
                    WHERE p2.ID IN (
    $q5)
                    "
    ;
                
    $q3 "
                    SELECT DISTINCT tt3.*
                    FROM
                        
    $wpdb->posts p3
                        INNER JOIN 
    $wpdb->term_relationships tr3 ON (p3.ID = tr3.object_id)
                        INNER JOIN 
    $wpdb->term_taxonomy tt3 ON (tr3.term_taxonomy_id = tt3.term_taxonomy_id)
                        INNER JOIN 
    $wpdb->terms t3 ON t3.term_id = tt3.term_id
                    WHERE 1=1
                        AND tt3.term_id IN (
    $q2)
                        AND tt3.taxonomy = 'product_brand'
                    ORDER BY t3.name ASC
                    "
    ;
            
    $brands $wpdb->get_results($q3); 
    The first two queries runs fine but the last one make the datbase query too slow
    so any help please

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,616
    Sounds like at least once column being used in the JOIN and/or WHERE clause is not indexed. Otherwise, I don't see anything obviously problematic.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Oct 2011
    Location
    Pakistan
    Posts
    16
    Hi There Thanx a lot for the reply
    The real problem is i dont which indeing you are talking about
    i have read several thread about this problem and all are talking about the same thing indexing xan u please explain that how can i use that in my queries please?

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,616
    This would be something you'd address in the actual database table definition itself, probably most easily done through phpMyAdmin if your web host provides that for your database maintenance. Any table column which is tested in a WHERE, JOIN, or ORDER BY clause should be indexed, either as a simple INDEX, a UNIQUE index, or as the PRIMARY KEY. (And in some instances you may want to create a single index across multiple columns, but for now at least I would not worry about it.

    If you bring up your wordpress DB in phpMyAdmin, check each table that is used in your query and check the index setting for each column you use in any of the above SQL sections. If it does not have any index activated, select the "index" option - you probably don't want to use "unique" or "primary" unless you know for sure that's what it should be. Save your changes, then see if that improves performance.

    If you want to really understand this, you could start with: http://dev.mysql.com/doc/refman/5.1/...n-indexes.html
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles