MySQL Join Optimisation - 2 hours, for what should take seconds!!

Peter_Zhang

Hi,

I have been trying to optimise a MySQL script, which for some reason is taking over 2 hours to run! I've been unable to figure out why the script takes so long to run, so any help and tips for identifying the cause of the problem would be appreciated.

The script is very simple, essentially:

SELECT x.*, y.field1, y.field2, y.field3

FROM table1 x

LEFT JOIN table2 y

on x.date = y.date and x.id = y.id and x.id2 = y.id2

Both tables are approximately 90k rows.

GrantSmith

Have you used an SQL transform to add indexes to your two tables on id, id2 and date fields?

Alternatively have you used the new MagicETL as that will be much faster.