Optimizing MySQL self-join query <p>I have following query:</p> <pre><code>SELECT DISTINCT(a1.actor) FROM actions a1 JOIN actions a2 ON a1.ip = a2.ip WHERE a2.actor = 143 AND a2.ip != '0.0.0.0' AND a2.ip != '' AND a2.actor != a1.actor AND a1.actor != 0 </code></pre> <p>This is the explain of the query:</p> <pre><code>+----+-------------+-------+-------+------------------+---------+---------+------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+------------------+---------+---------+------------------+------+--------------------------+ | 1 | SIMPLE | a2 | range | actor,ip,actorIp | actorIp | 66 | NULL | 3800 | Using where; Using index | | 1 | SIMPLE | a1 | ref | ip | ip | 62 | formabilio.a2.ip | 11 | Using where | +----+-------------+-------+-------+------------------+---------+---------+------------------+------+--------------------------+ </code></pre> <p>Even if from this it doesn't seem a problematic query, in my machine it takes more or less 69 seconds with MyIsam and 56 seconds in InnoDB. The table has more or less 1 thousand records. As you can see from the explain I have indeces both on the actor column, on the ip column and even on both columns. I have mysql version 5.5.35.</p> <p>Do you have any idea on why this query takes so long? How can I optimize it?</p>