title: Large Table Query via MySQL with WHERE across two tables
body: <p>I have two large tables with the a <code>venue_id</code> across both of them. It is not a foreign key, as they are two independent values. They are as follows:</p>
  
  <pre><code>table: activity_retail
  columns: activity_id, venue_id, created_at
  indexes: activity_id (PRIMARY), (activity_id, venue_id) INDEX
  
  
  table: activity
  columns: activity_id, item_id, venue_id, created_at
  indexes: activity_id (PRIMARY), (created_at) INDEX
  </code></pre>
  
  <p>I would like to do a query across both of them, to all activities where <code>venue_id</code> equals <code>X</code> in each table. So I would do:</p>
  
  <pre><code>    SELECT * from activity
      LEFT JOIN activity_retail on activity_retail.activity_id = activity.activity_id
      WHERE activity_retail.venue_id = X or activity.venue_id = X 
  ORDER BY activity_retail.created_at desc LIMIT 0, 25
  </code></pre>
  
  <p>I also may need to do this query for pagination:</p>
  
  <pre><code>    SELECT * from activity
      LEFT JOIN activity_retail on activity_retail.activity_id = activity.activity_id
      WHERE activity_retail.venue_id = X or activity.venue_id = X and activity.activity_id &gt; Y
  ORDER BY activity_retail.created_at desc LIMIT 0, 25
  </code></pre>
  
  <p>The situation is the <code>activity_id</code> in <code>activity_retail</code> may not exist, it's an optional field, so I can't do an inner join, has to be a left join.</p>
  
  <p>Here is the <code>EXPLAIN</code>:</p>
  
  <pre><code>1   PRIMARY activity    index   ix_ivi_created created_at   8   NULL    25  Using where
  1   PRIMARY activity_retail eq_ref  PRIMARY PRIMARY 4   activity.activity_id    1   
  </code></pre>
  
  <p>While it's small query (according to <code>EXPLAIN</code>), it takes a very long time to execute, and doesn't use anything in the <code>EXTRAS</code> area.</p>
  
  <p>Any advice on how to get this to work correctly - basically a <code>WHERE</code> condition across two tables with an <code>OR</code> statement? <code>AND</code> works really well, but we need <code>OR</code> here.</p>