title: SQL optimizing large or query
body: <p>I'm attempting to run a query to find any matches between multiple phone number columns on two tables and it is taking far too long (>5 minutes) and this is with the data filtered as much as possible. I've separated the actual columns I can search from both tables into their own tables, just to reduce the amount of total rows.</p>
  
  <p><strong>This is from a legacy application I inherited.</strong></p>
  
  <p><strong>Query</strong></p>
  
  <pre><code>select count(b.bid) 
  from customers_with_phone c,buyers_orders_with_phone b 
  where 
     (b.hphone=c.pprim or b.hphone=c.phome or b.hphone=c.pwork or b.hphone=c.pother) 
  or (b.wphone=c.pprim or b.wphone=c.phome or b.wphone=c.pwork or b.wphone=c.pother) 
  or (b.cphone=c.pprim or b.cphone=c.phome or b.cphone=c.pwork or b.cphone=c.pother) 
  group by b.bid;
  </code></pre>
  
  <p><strong>Tables</strong></p>
  
  <pre><code>mysql&gt; show columns from customers_with_phone;
  +--------+---------+------+-----+---------+-------+
  | Field  | Type    | Null | Key | Default | Extra |
  +--------+---------+------+-----+---------+-------+
  | pnum   | int(11) | YES  |     | NULL    |       |
  | pprim  | text    | YES  |     | NULL    |       |
  | phome  | text    | YES  |     | NULL    |       |
  | pwork  | text    | YES  |     | NULL    |       |
  | pother | text    | YES  |     | NULL    |       |
  +--------+---------+------+-----+---------+-------+
  
  mysql&gt; show columns from buyers_orders_with_phone;
  +--------+------+------+-----+---------+-------+
  | Field  | Type | Null | Key | Default | Extra |
  +--------+------+------+-----+---------+-------+
  | bid    | text | YES  |     | NULL    |       |
  | hphone | text | YES  |     | NULL    |       |
  | wphone | text | YES  |     | NULL    |       |
  | cphone | text | YES  |     | NULL    |       |
  +--------+------+------+-----+---------+-------+
  </code></pre>
  
  <p><strong>Explain</strong></p>
  
  <pre><code>+----+-------------+-------+------+---------------+------+---------+------+-------+----------+----------------------------------------------+
  | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                                        |
  +----+-------------+-------+------+---------------+------+---------+------+-------+----------+----------------------------------------------+
  |  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL |  8673 |   100.00 | Using where; Using temporary; Using filesort |
  |  1 | SIMPLE      | c     | ALL  | NULL          | NULL | NULL    | NULL | 75931 |   100.00 | Using where; Using join buffer               |
  +----+-------------+-------+------+---------------+------+---------+------+-------+----------+----------------------------------------------+
  </code></pre>
  
  <p>I realize that neither tables have a primary key, as these are only the columns that I need to search on and I extracted these columns from their original table. But using the original table it takes even longer because there is far more data to filter through.</p>
  
  <p>I have other queries that are similar to this that will work with much more data so if I can make this one work in a reasonable time, I can get the others to work similarly.</p>