title: mysql select query optimization
body: <p>I have two table testa &amp; testb.</p>
  
  <pre><code>CREATE TABLE `testa` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) DEFAULT NULL,
    PRIMARY KEY (`id`)
  );
  
  CREATE TABLE `testb` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) DEFAULT NULL,
    `aid1` INT(10) DEFAULT NULL,
    `aid2` INT(10) DEFAULT NULL,
    `aid3` INT(10) DEFAULT NULL,
    PRIMARY KEY (`id`)
  );
  </code></pre>
  
  <p>Currently I am running below query for retrieving all rows where id in testa table matches with any columns of aid1,aid2,aid3 in tableb. The query is retreiving acurate result but it is taking minimum 30 seconds to execute which is too much. I have also tried to optimise my query using UNION but failed to do so.</p>
  
  <pre><code>SELECT a.id, a.name, b.name, b.id 
  FROM testb b 
  INNER JOIN testa a ON b.aid1 = a.id OR b.aid2 = a.id OR b.aid3 = a.id ;
  </code></pre>
  
  <p>How do i optimize my query so it's total execution time is within 2-3 seconds?</p>
  
  <p>Thanks in advance...</p>
  
  <p>Result of EXPLAIN:</p>
  
  <pre><code>id  select_type table   type    possible_keys   key key_len ref rows    Extra
  1   SIMPLE  b   ALL idx_aid1,idx_aid2,idx_aid3  (NULL)  (NULL)  (NULL)  10940   
  1   SIMPLE  a   ALL PRIMARY (NULL)  (NULL)  (NULL)  7512    Using where; Using join buffer
  </code></pre>