title: MySql Join condition with Where condition, execution order and performance
body: <p>Im writing a large query, which must query data from many tables.
  ( 11 tables )</p>
  
  <p>I'm rewriting a script, which has huge performance issues. 
  The script makes thousands( sometimes tens of thousands ) of small queries instead of few large ones. The query can take hours to finish, and can consume massive amounts of memory.
  The way it works at the moment is this.
  1) Query matching events from db
  2) i.e 500 rows were returned.
  3) loop over the rows, and fetch additional data from each associated table</p>
  
  <pre><code>   foreach(rows as row) {
        row.user = /* query db for user data */
        row.products = /* query db for product data */
        row.productMeta = /* query db for additional data */
        ... and so on, for 11 tables
     }
  </code></pre>
  
  <p>So, the 500 rows will create 500 x 11 queries.</p>
  
  <p>My goal is to rewrite the script, in a way that it 
  joins and evaluates all the conditions on MySql server side, in 1-2 large queries.</p>
  
  <p>Here is the question.
  What is the most efficient way to make this large query.
  Does it make a difference, if i evaluate conditions in
  Join ON clause, compared to outer WHERE clause.</p>
  
  <pre><code>i.e
  
  SELECT * FROM events
    INNER JOIN users ON users.event_id = events.id AND &lt;condition 1&gt;
    INNER JOIN products ON products.event_id = events.id AND &lt;condition 2&gt;
    LEFT JOIN inventory ON inventory.event_id = events.id AND &lt;condition 3&gt;
  
  VS
  
  SELECT * FROM events
    INNER JOIN users ON users.event_id = events.id
    INNER JOIN products ON products.event_id = events.id
    LEFT JOIN inventory ON inventory.event_id = events.id
  WHERE 
    &lt;condition 1&gt; 
    AND &lt;condition 2&gt; 
    AND &lt;condition 3&gt;
  
  VS 
  
  making a few more queries to pre-filter the events, then join 
  
  $userIds = "make separate join to return event ids where users.event_id = events.id AND &lt;condition 1&gt;"
  $productIds = "make separate join to return event ids where products.event_id = events.id AND &lt;condition 2&gt;"
  
  Then:
  
  SELECT * FROM events
    INNER JOIN users ON users.event_id = events.id
    INNER JOIN products ON products.event_id = events.id
    LEFT JOIN inventory ON inventory.event_id = events.id
  WHERE 
    events.id IN ($userIds) OR events.id IN ($productIds)
  </code></pre>
  
  <p>PS: All related columns are indexed</p>