How do I find the join size of a mysql query?

Question

I had a query that was resulting in the error:

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.

I have now changed the query, and I no longer get this error. max_join_size = 900,000 and sql_big_selects = 0. Unfortunately I don't have ssh access, I have to use phpmyadmin.

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.

EDIT: This was the original query:

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;

I can temporarily enable SQL_BIG_SELECTS to get the EXPLAIN to run - here is the output:

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

In rewriting the query, I just split it up to run two separate queries, first to find client_id (e.g. 226) and timesheet_id (e.g. 75) from tz_verify, then used these values in this query:

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;

Here is the EXPLAIN:

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

This doesn't seem as neat though as doing it in one go!

Answer 1

Based on the first output of EXPLAIN you posted:

• I think the join size is: 93*559*185 = 9 617 595
• your initial query seems to not be always using indexes when joining with tables tz_sessions and tz_tutor_comments. I suggest adding the following compound indexes (each index is made of 2 fields):
  • table tz_verify: (timesheet_id, client_id)
  • table tz_sessions: (timesheet_id, client_id)
  • table tz_tutor_comments: (timesheet_id, client_id)

If one of these indexes already exist, do not create it again.

Once you added the indexes, run the EXPLAIN again (using your initial query). You will notice that the query is now using indexes for each join (look in the column "key"). You may run your initial query again, it should no longer cause the error you had.

Documentation:

Optimizing Queries with EXPLAIN
CREATE INDEX Syntax
How MySQL Uses Indexes
Multiple-Column Indexes