Hi,
As Peter writes join conditions with OR does not optimize too well. However, MySQL will still try to use index. "Range checked for each record" means that for every row of the left table, the optimizer will evaluate what index to use.
However, the only appropriate index for sfoa_1 is the index on parent_id for condition "sfoa_1.parent_id < sfo.entity_id". In other words, all rows in sfoa_1 with parent_id less than sfo.entity_id will have to be checked for every row of the outer table. I suggest you try to add index on email and telephone columns to see if that can improve the execution time.
I am a bit surprised that EXPLAIN says "Range checked for each record" for sfoi. I would think it should be possible to use look-up on PRIMARY KEY in that case. It would be interesting to see the optimizer trace for this query to understand what is going on. (See
https://dev.mysql.com/doc/internals/en/optimizer-tracing.html for how to obtain optimizer trace.) You could also try to see if it makes any difference to move the LEFT JOIN of sfoi ahead of the "self-joins" with sfoa_1 and sfo_1.
As to the query itself, I wonder whether it might be possible and more efficient to use subqueries instead of the "self-joins". I must admit I do not fully understand what the query does, but it seems to me that packing things together with group_concat and then checking for existence of a sub-string would be better solved by an IN-subquery.
Regards,
Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway