CODEWITHSUNDEEP
mysql
Bounce
Bounce

Reputation: 2095

Multiple left joins and performance

I have following tables:

products - 4500 records

Fields: id, sku, name, alias, price, special_price, quantity, desc, photo, manufacturer_id, model_id, hits, publishing

products_attribute_rel - 35000 records

Fields: id, product_id, attribute_id, attribute_val_id

attribute_values - 243 records

Fields: id, attr_id, value, ordering

manufacturers - 29 records

Fields: id, title,publishing

models - 946 records

Fields: id, manufacturer_id, title, publishing

So I get data from these tables by one query:

SELECT jp.*,
       jm.id AS jm_id,
       jm.title AS jm_title,
       jmo.id AS jmo_id,
       jmo.title AS jmo_title
FROM `products` AS jp 
LEFT JOIN `products_attribute_rel` AS jpar ON jpar.product_id = jp.id
LEFT JOIN `attribute_values` AS jav ON jav.attr_id = jpar.attribute_val_id
LEFT JOIN `manufacturers` AS jm ON jm.id = jp.manufacturer_id
LEFT JOIN `models` AS jmo ON jmo.id = jp.model_id 
GROUP BY jp.id HAVING COUNT(DISTINCT jpar.attribute_val_id) >= 0

This query is slow as hell. It takes hundreds of seconds mysql to handle it. So how it would be possible to improve this query ? With small data chunks it works perfectly well. But I guess everything ruins products_attribute_rel table, which has 35000 records.

Your help would be appreciated.

EDITED

EXPLAIN results of the SELECT query:

EXPLAIN results of the SELECT query

Upvotes: 4

Views: 10362

Answers (3)

Mike
Mike

Reputation: 2587

The problem is that MySQL uses the join-type ALL for 3 tables. That means that MySQL performs 3 full table scans, puts every possibility together before sorting those out that don't match the ON statement. To get a much faster join-type (for instance eq_ref), you must put an index on the coloumns that are used on the ON statements.

Be aware though that putting an index on every possible coloumn is not recommended. A lot of indexes do speed up SELECT statements, however it also creates an overhead since the index must be stored and managed. This means that manipulation queries like UPDATE and DELETE are much slower. I've seen queries deleting only 1000 records in half an hour. It's a trade-off where you have to decide what happens more often and what is more important.

To get more infos on MySQL join-types, take a look at this.
More on indexes here.

Upvotes: 13

chinna_82
chinna_82

Reputation: 6403

select distinct
jm.id AS jm_id,
jm.title AS jm_title,
jmo.id AS jmo_id,
jmo.title AS jmo_title
from products jp, 
products_attribute_rel jpar,
attribute_values jav,
manufacturers jm
models jmo
where jpar.product_id = jp.id
and jav.attr_id = jpar.attribute_val_id
and jm.id = jp.manufacturer_id
and jmo.id = jp.model_id

you can do that if you want to select all the data. Hope it works.

Upvotes: 0

user319198
user319198

Reputation:

Tables data is not so much huge that it's taking hundreds of seconds. Something is wrong with table schema. Please do proper indexing. That will surly speed up.

Upvotes: 0

Related Questions