Multiple Left Joins and Performance

Question

I have the following tables:

products - 4500 records

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

products_attribute_rel - 35,000 entries

Fields: id, product_id, attribute_id, attribute_val_id

attribute_values ​​- 243 entries

Fields: id, attr_id, value, order

manufacturers - 29 entries

Fields: id, title, publication

models - 946 entries

Fields: id, manufacturer_id, name, publication

So, I get data from these tables for 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 request is slow as hell. Processing takes a few seconds of mysql. So how can this query be improved? With small pieces of data, it works perfectly. But I think all the ruins of the products_attribute_rel table, which has 35,000 entries.

Your help will be greatly appreciated.

EDITED

EXPLAIN SELECT query results:

Answer 1

The problem is that MySQL uses the ALL join type for the three tables. This means that MySQL performs 3 full table scans, puts each opportunity together before sorting those that do not match the ON statement. To get a much faster connection type (e.g. eq_ref ), you should put the index in coloumns, which are used in ON statements.

Keep in mind that it is not recommended to specify an index for all possible coloumn. Many indexes speed up SELECT , but also create overhead because the index must be stored and managed. This means that manipulation queries like UPDATE and DELETE are much slower. I saw queries deleting just 1000 records in half an hour. This is a compromise when you need to decide what happens more often and what is more important.

For more information on MySQL connection types, see this .
Read more about indexes here .

Answer 2

Data tables are not so large that they take hundreds of seconds. Something is wrong with the table layout. Please do the correct indexing. This will accelerate the acceleration.

Answer 3

 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 this if you want to select all the data. Hope this works.