Re: Three(or Four)-Way Joins

[Rick James]

-- FROM (SELECT...) is usually less efficient than JOIN

-- If anything, step 1 is to concentrate on finding the ids needed. Then step 2 is to fetch the thousands of columns needed. That is:
Step 1: CREATE TEMPORARY TABLE tmp (SELECT id FROM ... JOIN ... WHERE ...);
Step 2:

SELECT ...
   FROM tmp
   JOIN data1 ON data1.id = tmp.id
   JOIN ...

Yes, sometimes using a tmp table is optimal.

-- "So is it optimal?" ... to answer that, please provide
* SHOW CREATE TABLE tbl\G
* SHOW TABLE STATUS LIKE 'tbl'\G
* EXPLAIN SELECT ...\G