Mysql – IF condition on temporary table takes long time for query execution

MySQL

SELECT actualTable.column1, actualTable.column2, actualTable.column3
FROM table1 actualTable LEFT JOIN (
    SELECT column1, column2
    FROM table2
    GROUP BY column1
    ORDERBY ASC
) temp
WHERE IF(temp.column1 IS NOT NULL, temp.column1, actualTable.column1)
AND IF(temp.column2 IS NOT NULL, temp.column2, actualTable.column2)
GROUP BY actualTable.column1;

Above query is for reference. I have used 6 tables and added necessary joins.
The problem is that IF condition on Temporary table in Where cluase takes time. If i remove it then it takes less time to execute.

Best Answer

It is improper to "group by col1" but fetch col2 unless they col2 is either directly derivable from col1 or is an "aggregate".

The ORDER BY in the derived table will be ignored (once the two syntax errors are fixed). Why do you have it?

table2, as the query stands, needs INDEX(col1, col2).

Break the query up into two in order to avoid the IF clauses. Something like

SELECT ... JOIN ...
UNION
SELECT ... LEFT JOIN WHERE temp.col1 IS NULL AND ...

That will need parens around it before doing the outer GROUP BY.

Related Question