MySQL Forums
Forum List  »  Performance

Re: Query optimization needed
Posted by: Øystein Grøvlen
Date: November 17, 2015 05:43AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
1728
November 05, 2015 01:42AM
662
November 05, 2015 11:40PM
757
November 06, 2015 11:25PM
791
November 08, 2015 04:32PM
Re: Query optimization needed
727
November 17, 2015 05:43AM
728
November 26, 2015 09:21PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.