title: How do I find the join size of a mysql query?
body: <p>I had a query that was resulting in the error:</p>
  
  <pre>ERROR 1104: The SELECT would examine more rows than MAX_JOIN_SIZE. 
  Check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is ok.</pre>
  
  <p>I have now changed the query, and I no longer get this error. <code>max_join_size = 900,000</code> and <code>sql_big_selects = 0</code>. Unfortunately I don't have ssh access, I have to use phpmyadmin.</p>
  
  <p>So my question is, is there any way of determining how many rows a particular query would examine? I would like to see how close a query is to the max_join_size limit.</p>
  
  <p>EDIT: This was the original query:</p>
  
  <pre><code>SELECT * FROM `tz_verify` 
  LEFT JOIN `tz_sessions` ON `tz_sessions`.`timesheet_id` = `tz_verify`.`timesheet_id`
  AND `tz_sessions`.`client_id` = `tz_verify`.`client_id`
  LEFT JOIN `tz_clients` ON `tz_sessions`.`client_id` = `tz_clients`.`id`
  LEFT JOIN `tz_tutor_comments` ON `tz_sessions`.`timesheet_id` = `tz_tutor_comments`.`timesheet_id`
  AND `tz_sessions`.`client_id` = `tz_tutor_comments`.`client_id` 
  LEFT JOIN `tz_notes` ON `tz_sessions`.`notes` = `tz_notes`.`id` 
  WHERE `tz_verify`.`code` = 'b65f35601c' AND `confirmed` = 0;
  </code></pre>
  
  <p>I can temporarily enable <code>SQL_BIG_SELECTS</code> to get the <code>EXPLAIN</code> to run - here is the output:</p>
  
  <pre><code>id  select_type  table              type    possible_keys  key      ref                    rows  extra
  1   SIMPLE       tz_verify          ALL     NULL           NULL     NULL                   93    Using where
  1   SIMPLE       tz_sessions        ALL     NULL           NULL     NULL                   559
  1   SIMPLE       tz_clients         eq_ref  PRIMARY        PRIMARY  tz_sessions.client_id  1
  1   SIMPLE       tz_tutor_comments  ALL     NULL           NULL     NULL                   185
  1   SIMPLE       tz_notes           eq_ref  PRIMARY        PRIMARY  tz_sessions.notes      1
  </code></pre>
  
  <p>In rewriting the query, I just split it up to run two separate queries, first to find <code>client_id</code> (e.g. 226) and <code>timesheet_id</code> (e.g. 75) from <code>tz_verify</code>, then used these values in this query:</p>
  
  <pre><code>SELECT * FROM `tz_sessions` 
  LEFT JOIN `tz_clients`
  ON `tz_clients`.`id` = 226
  LEFT JOIN `tz_tutor_comments` 
  ON `tz_tutor_comments`.`timesheet_id` = 75
  AND `tz_tutor_comments`.`client_id` = 226
  LEFT JOIN `tz_notes` 
  ON `tz_sessions`.`notes` = `tz_notes`.`id`
  WHERE `tz_sessions`.`client_id` = 226 AND `tz_sessions`.`timesheet_id` = 75;
  </code></pre>
  
  <p>Here is the <code>EXPLAIN</code>:</p>
  
  <pre><code>id  select_type  table              type    possible_keys  key      ref                    rows  extra
  1   SIMPLE       tz_sessions        ALL     NULL           NULL     NULL                   559   Using where
  1   SIMPLE       tz_clients         const   PRIMARY        PRIMARY  const                  1
  1   SIMPLE       tz_tutor_comments  ALL     NULL           NULL     NULL                   185
  1   SIMPLE       tz_notes           eq_ref  PRIMARY        PRIMARY  tz_sessions.notes      1
  </code></pre>
  
  <p>This doesn't seem as neat though as doing it in one go!</p>