Subqueries run very fast individually, but when joined are very slow

ypercube solved the problem. Subqueries were totally unnecessary, and the whole thing works with plain joins. It is still strange that MySQL's optimizer could not make use of my original query, though. See below for the question and lots of details. Plus a complete solution at the bottom of my question. It is based on ypercube's answer.

Each subquery is very fast, well under 1 second.
The 5-6 subqueries are joined (some LEFT, some INNER), and the time mushrooms to 400 seconds.

The overall query I'm using for testing returns only 441 rows.

I tried putting each of the subqueries in a "CREATE TABLE" query. Each one was done in well under 1 second. Then I redid the outer query using those newly created tables, and it also ran in well under 1 second. So there is no actual problem with the joins. I put indexes on id for my created tables. All tables are joined on matching id=id.

How can I make MySQL perform the query efficiently? Must I use temporary tables? I've already written a bunch of PHP code to put together the multiple subquery joins so I would rather just figure out how to make that work, if possible.

I tried using "STRAIGHT_JOIN" keyword and removing the outer ORDER BY. That reduced query time to 90s. But I should be getting 1s max.

I tried STRAIGHT_JOIN with ORDER BY and it took 235 seconds. So it seems like the outer ORDER BY is a major performance issue.

EDIT:

Tested using temporary tables. Query runs very fast. But there has got to be a way to make mysql do it that fast with JOINS.

Also, slow query log shows:

Rows_examined: 484006914

484 million rows looks like a cartesian product. Why is it examining so many rows?

The query has this structure:

``
SELECT t0.id, t1.length, t2.height, t3.family
FROM
products t0
INNER JOIN
(
SELECT t1.id, t2.value AS length
FROM products t1
INNER JOIN product_eav_decimal t2
ON t1.id = t2.product_id
WHERE t2.attribute_id = 91
AND t2.value` BETWEEN 15 AND 35
) t1

You don't need all the derived tables. You are joining the basic (product) too many times. You can write the query joining it only once.

Compound indices are a must for EAV designs. Try adding an index on (attribute_id, product_id, value) and then the query:

SELECT t0.id, 
       t1.`value` AS length, 
       t2.`value` AS height, 
       t3.`value` AS family
FROM
  products t0

INNER JOIN 
  product_eav_decimal t1
    ON  t1.product_id = t0.id  
    AND t1.attribute_id = 91
    AND t1.`value` BETWEEN 15 AND 35

LEFT JOIN
  product_eav_decimal t2
    ON  t2.product_id = t0.id  
    AND t2.attribute_id = 80  
-- 
-- 
--

LEFT JOIN                              -- LEFT or INNER join
  product_eav_decimal t6
    ON  t6.product_id = t0.id  
 -- AND t6.attribute_id = 

ORDER BY t0.id ASC ;

StackExchange Database Administrators Q#31828, answer score: 6