Re: Slow query

[Øystein Grøvlen]

Hi,

As Rick writes, MySQL can not use index when join condition contains OR and you need to rewrite.

Another issue I would like to point out is that in MySQL 5.6 and later, the query optimizer is be better at optimizing IN expressions than EXISTS expressions. Hence, the best practice is to write your query as:

SELECT * FROM clients
WHERE clients.client_id IN (SELECT archive.seller FROM archive)
OR clients.client_id IN (SELECT archive.buyer FROM archive);

In this particular case the performance will probably be the same, but in other cases there might be a significant difference.

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway