How to Improve Query Performance with many JOINs

mysql sql performance select query-optimization

25,609

Solution 1

Your performance issue is most likely caused by the join with the 'term_taxonomy' table.
All other joins seems to use the primary key (where you probobly have working indexes on).

So my suggestion is to add a compound index on term_taxonomy_id and term_id (or if you must: taxonomy). Like this:

CREATE UNIQUE INDEX idx_term_taxonomy_id_taxonomy
ON term_taxonomy( term_taxonomy_id, taxonomy);

Hope this will help you.

Solution 2

Make Sure all the columns on which there is "ON" conditional statements is there, should be indexed. This will significantly improve the speed.

25,609

dloewen

I’m a freelance web developer & designer living in Winkler, MB - out to empower businesses, organizations, and individuals to accomplish their purpose while accomplishing mine - that two way relationship leads to great things. The part I have to play is helping you communicate to your audience effectively, primarily online. In my spare time I enjoy creating/playing music, throwing together good food, and getting outdoors.

Updated on January 17, 2020

Comments

dloewen over 4 years
I have a query (with the purpose of making a view) which is using a few joins to get each column. Performance degrades quickly (exponentially?) for each set of joins added.

What would be a good approach to make this query faster? Please see comments within the query.

If it helps, this is using the WordPress DB schema.

Here is a screenshot of EXPLAIN

PRODUCTS TABLE
```
+--+----+
|id|name|
+--+----+
|1 |test|
+--+----+
```
METADATA TABLE
```
+----------+--------+-----+
|product_id|meta_key|value|
+----------+--------+-----+
|1         |price   |9.99 |
+----------+--------+-----+
|1         |sku     |ABC  |
+----------+--------+-----+
```
TERM_RELATIONSHIPS TABLE
```
+---------+----------------+
|object_id|term_taxonomy_id|
+---------+----------------+
|1        |1               |
+---------+----------------+
|1        |2               |
+---------+----------------+
```
TERM_TAXONOMY TABLE
```
+----------------+-------+--------+
|term_taxonomy_id|term_id|taxonomy|
+----------------+-------+--------+
|1               |1      |size    |
+----------------+-------+--------+
|2               |2      |stock   |
+----------------+-------+--------+
```
TERMS TABLE
```
+-------+-----+
|term_id|name |
+-------+-----+
|1      |500mg|
+-------+-----+
|2      |10   |
+-------+-----+
```
QUERY
```
SELECT 
  products.id,
  products.name,
  price.value AS price,
  sku.value AS sku,
  size.name AS size
FROM products

/* These joins are performing quickly */

INNER JOIN `metadata` AS price ON products.id = price.product_id AND price.meta_key = 'price'
INNER JOIN `metadata` AS sku ON products.id = sku.product_id AND sku.meta_key = 'sku'

/* Here's the part that is really slowing it down - I run this chunk about 5 times with different strings to match */

INNER JOIN `term_relationships` AS tr ON products.id = tr.object_id
  INNER JOIN `term_taxonomy` AS tt
  ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = 'size'
    INNER JOIN `terms` AS size
    ON tt.term_id = size.term_id
```
- KM. over 10 years
  
  OR can kill index usage, try to rewrite using a union.
- Manu over 10 years
  
  show the output of DESC tableName for each table.
- Manu over 10 years
  
  or atleast output of EXPLAIN <your SQL>;
- dloewen over 10 years
  
  @Manu I added an image of the EXPLAIN results to the question. Thanks.
- dloewen over 10 years
  
  Also, I tried re-writing using subqueries for each column, but then PHPMyAdmin gives me "Commands out of sync; you can't run this command now"...
- KM. over 10 years
  
  @dloewen, I must have been seeing things, I remember your code having an "OR" it in it, oh well just ignore my previous comment.
- Luis Gonzalez over 10 years
  
  it seems like the products.meta_key nor the term_taxonomy.taxonomy fields are not indexed. Could you please confirm that they are?
- Tomas over 10 years
  
  KM & dloewen - so please clean up your obsolete comments..
- Tomas over 10 years
  
  dloewen, are you sure the explain is of the same query as you present? Explain says "using where", but your query doesn't contain where clause! It is important that you provide us the exactly the same query!
- Tomas over 10 years
  
  The explain for sure is of a different query! Tables tt2, tt3 are not present. -1 until you fix.
- Meherzad over 10 years
  
  Have a index on taxonomy column and put the tt.taxonomy = 'size' condition before inner join so that you can get benefit from the short circuit operation. tt.taxonomy = 'size' AND tr.term_taxonomy_id = tt.term_taxonomy_id. PLease provide proper exaplain statement of your given query.
Strawberry over 10 years

Note that CASE conforms to the SQL standard.
carleson over 10 years

I can´t see the point of doing this ? The performance will gets even worse in this case, since you have the same query and then also uses a temp table. Temp tables Could speed up slow queries if you split up big queries into smaller sub-sets, but that is not the case here.
Karolis over 10 years

This could work. +1. Also it would be a good idea to make a compound index for metadata (product_id, mate_key) because having a wide index diversity means there are more options for mysql optimizer to create a better execution plan.
dloewen over 10 years

All tables have a primary key, and each column is indexed that I'm joining with.
dloewen over 10 years

Thanks, I want to try this but there is no PIVOT in MySQL, so I'm not sure how to adapt this.
dloewen over 10 years

Bingo! Creating the compound index in the metadata table on product_id and meta_key made the query execute in under 1 second, a huge improvement from the 30+ seconds!
dloewen over 10 years

@Karolis can you post your solution separately so I can give you the bounty? Thanks also carleson for the tip that lead to the solution.
Karolis over 10 years

@dloewen Well, I think carleson gave you the right direction and my comment was just 2 cents in order to make his answer more comprehensive :-)