Want to Get Brands In Wordpress Database Structure Saved as Custom Post types

Question

I am having difficulty in fetching the brands per category 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 to show specific brands in the side bar of a specific category not the whole brands list which is 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 database too slow and the website keeps loading without showing brands the query is working fine the only thing I want to know is to speed up the queries for the customers so they don't fade away.

Here are the queries I am using:

$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 run fine but the last one makes the database query too slow.

How can I make the last query run faster?

Answer 1

There are several potential issues here, and without an explain like RolandoMySQLDBA asks for, it's really hard to know.

However, when looking at the difference between the queries, several things stick out:

1. You're joining with terms. If either terms.term_id or term_taxonomy.term_id isn't indexed, it will affect query efficiency. I have no idea about how Wordpress is set up with indexing by default or if you've added any extra indexes.

2. You're selecting * from tt3. Do you really need all those columns? If not, use only the ones you need, and index those properly if possible.

3. How many results are you getting from $q2?

4. ORDER BYs are sometimes expensive. Depending on other queries run against this table, it might make sense to cluster terms on name ASC.