By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,568 Members | 1,688 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,568 IT Pros & Developers. It's quick & easy.

Inner Join Queries Working Too Slow How to Use Indexes in them

P: 1
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


Expand|Select|Wrap|Line Numbers
  1.                $term = get_term_by('slug',get_query_var('term'),'product_category');
  2.  
  3.  
  4.         $q5 = "SELECT DISTINCT p1.ID
  5.                 FROM
  6.                     $wpdb->posts p1
  7.                     INNER JOIN $wpdb->term_relationships tr1 ON (p1.ID = tr1.object_id)
  8.                     INNER JOIN $wpdb->term_taxonomy tt1 ON (tr1.term_taxonomy_id = tt1.term_taxonomy_id)
  9.                 WHERE tt1.term_id = '$term->term_id'
  10.                 ";
  11.             $q2 = "
  12.                 SELECT tt2.term_id
  13.                 FROM
  14.                     $wpdb->posts p2
  15.                     INNER JOIN $wpdb->term_relationships tr2 ON (p2.ID = tr2.object_id)
  16.                     INNER JOIN $wpdb->term_taxonomy tt2 ON (tr2.term_taxonomy_id = tt2.term_taxonomy_id)
  17.                 WHERE p2.ID IN ($q5)
  18.                 ";
  19.             $q3 = "
  20.                 SELECT DISTINCT tt3.*
  21.                 FROM
  22.                     $wpdb->posts p3
  23.                     INNER JOIN $wpdb->term_relationships tr3 ON (p3.ID = tr3.object_id)
  24.                     INNER JOIN $wpdb->term_taxonomy tt3 ON (tr3.term_taxonomy_id = tt3.term_taxonomy_id)
  25.                     INNER JOIN $wpdb->terms t3 ON t3.term_id = tt3.term_id
  26.                 WHERE 1=1
  27.                     AND tt3.term_id IN ($q2)
  28.                     AND tt3.taxonomy = 'product_brand'
  29.                 ORDER BY t3.name ASC
  30.                 ";
  31.         $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
Jan 8 '13 #1

✓ answered by Rabbit

It is unlikely that it is the join that's the cause of the slowness. A more likely culprit is the IN clause you're using. IN clauses can be extremely slow and are often better expressed as joins. You should rewrite your query using a join instead of an IN and that could fix your issue.

Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,430
It is unlikely that it is the join that's the cause of the slowness. A more likely culprit is the IN clause you're using. IN clauses can be extremely slow and are often better expressed as joins. You should rewrite your query using a join instead of an IN and that could fix your issue.
Jan 8 '13 #2

Post your reply

Sign in to post your reply or Sign up for a free account.