Re: Slow join performance

[Rick James]

OK, you need the LEFTs. The reason for asking if they could be removed -- There are filtering criteria on 3 of the tables that come late in the EXPLAIN. If they could come first, then the query would probably run a lot faster.

As you have it (with LEFT everywhere), you are expecting 4M rows of output, correct? That, in itself, will take a long time. (Seconds, at least.)

I'm still puzzled at the logic -- When "ea2.address_type = 'Bil'", you get the address fields displayed in the output. When it is != 'Bil', you will get NULLs. This is not a typical way to do a WHERE clause. Usually you would want to skip the row completely. Please explain.

Perhaps the LEFT could (should) be removed from rn, be1, bea1, ea2 and make sure the indexes let the query start them them _before_ getting to the 4M-row psrl.