title: Optimising MySQL LEFT JOIN query for simple three table schema?
body: <p>I've written a job request system in PHP with a MySQL database and I'm having an issue with a slow query.</p>
  
  <p>My schema (simplified) is as follows:</p>
  
  <p><strong>tbl_job</strong><br>
  job_id<br>
  job_desc
  requester_user_id</p>
  
  <p><strong>tbl_user</strong><br>
  user_id<br>
  user_name  </p>
  
  <p><strong>tbl_workermap</strong><br>
  workermap_id<br>
  job_id<br>
  worker_user_id</p>
  
  <p>A table containing the jobs, a user table for the possible workers and one to map workers to jobs. A job can have one or more workers, a worker can have one or more jobs.</p>
  
  <p>tbl_user contains both users who request work, and those that work on the jobs, so user IDs are stored under worker_user_id in tbl_workermap and requester_user_id in tbl_job</p>
  
  <p>When a job is logged it creates an entry in tbl_job but nothing in tbl_workermap until someone specifically assigns a worker. This means that when I query the jobs I do it with a left join as there are not entries in tbl_workermap for every job:</p>
  
  <pre><code>SELECT 
  job.job_id,
  job.job_desc,
  workermap.worker_user_id,
  worker.worker_name
  
  FROM tbl_job AS job
  
  LEFT JOIN tbl_workermap AS workermap
  ON job.job_id = workermap.job_id
  
  LEFT JOIN tbl_user AS worker
  ON workermap.worker_user_id = worker.user_id
  </code></pre>
  
  <p>The system has been in use for a while and I now have about 8000 entries in tbl_job and 7000 in tbl_workermap and it's taking over 4 seconds to retrieve all results. An EXPLAIN query shows the tbl_workermap join returning around 7000 rows and "Using where; Using join buffer (Block Nested Loop)".</p>
  
  <p>Is there anything I can do to speed this up?</p>
  
  <p><strong>EDIT: add table info</strong><br>
  I'd simplified things to explain but here's the actual table structure. There are more joins but tbl_workermap is the only problematic one:</p>
  
  <pre><code>CREATE TABLE `tbl_job` (
    `job_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `job_title` varchar(100) DEFAULT NULL,
    `job_description` text,
    `job_added_datetime` int(11) DEFAULT '0',
    `job_due_datetime` int(11) NOT NULL DEFAULT '0',
    `job_time_estimate` int(11) DEFAULT NULL,
    `job_additional_fields` text,
    `addedby_user_id` int(11) NOT NULL DEFAULT '0',
    `requester_user_id` int(11) NOT NULL DEFAULT '0',
    `worker_user_id` int(11) NOT NULL DEFAULT '0',
    `job_active` tinyint(4) NOT NULL DEFAULT '1',
    `site_id` tinyint(4) NOT NULL DEFAULT '1',
    `status_id` int(11) NOT NULL DEFAULT '1',
    `estimategroup_id` int(11) DEFAULT '1',
    `brand_id` int(11) DEFAULT '1',
    `job_isproject` tinyint(4) DEFAULT NULL,
    PRIMARY KEY (`job_id`),
    FULLTEXT KEY `job_title` (`job_title`,`job_description`,`job_additional_fields`)
  ) ENGINE=MyISAM AUTO_INCREMENT=8285 DEFAULT CHARSET=latin1
  
  
  
  CREATE TABLE `tbl_user` (
    `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `user_shortname` varchar(30) DEFAULT NULL,
    `user_name` varchar(30) DEFAULT NULL,
    `user_password` varchar(50) DEFAULT NULL,
    `user_password_reset_uuid` varchar(50) DEFAULT NULL,
    `user_email` varchar(50) DEFAULT NULL,
    `user_description` text,
    `user_sortorder` int(11) NOT NULL DEFAULT '0',
    `user_isworker` tinyint(4) NOT NULL DEFAULT '0',
    `user_active` tinyint(4) NOT NULL DEFAULT '1',
    `site_id` tinyint(4) NOT NULL DEFAULT '0',
    `user_avatar_file_id` int(11) DEFAULT NULL,
    `user_avatar_hub_url` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`user_id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=917 DEFAULT CHARSET=latin1
  
  
  CREATE TABLE `tbl_workermap` (
    `workermap_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    `job_id` int(11) DEFAULT NULL,
    `workermap_datetime_added` int(11) DEFAULT NULL,
    `workermap_datetime_removed` int(11) DEFAULT NULL,
    `worker_user_id` int(11) DEFAULT NULL,
    `addedby_user_id` int(11) DEFAULT NULL,
    `removedby_user_id` int(11) DEFAULT NULL,
    `site_id` int(11) DEFAULT NULL,
    `workermap_isassigned` int(11) DEFAULT NULL,
    `workermap_active` int(11) NOT NULL DEFAULT '1',
    PRIMARY KEY (`workermap_id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=7145 DEFAULT CHARSET=latin1
  
  </code></pre>
  
  <p>SHOW INDEX</p>
  
  <pre><code>+---------+---+-----------+---+-----------------------+------+------+------+------+-----+----------+--+--+
  | tbl_job | 0 |  PRIMARY  | 1 |        job_id         |  A   | 8283 | NULL | NULL |     |  BTREE   |  |  |
  +---------+---+-----------+---+-----------------------+------+------+------+------+-----+----------+--+--+
  | tbl_job | 1 | job_title | 1 | job_title             | NULL |    1 | NULL | NULL | YES | FULLTEXT |  |  |
  | tbl_job | 1 | job_title | 2 | job_description       | NULL |    1 | NULL | NULL | YES | FULLTEXT |  |  |
  | tbl_job | 1 | job_title | 3 | job_additional_fields | NULL |    1 | NULL | NULL | YES | FULLTEXT |  |  |
  +---------+---+-----------+---+-----------------------+------+------+------+------+-----+----------+--+--+
  
  +----------+---+---------+---+---------+---+-----+------+------+--+-------+--+--+
  | tbl_user | 0 | PRIMARY | 1 | user_id | A | 910 | NULL | NULL |  | BTREE |  |  |
  +----------+---+---------+---+---------+---+-----+------+------+--+-------+--+--+
  
  +---------------+---+---------+---+--------------+---+------+------+------+--+-------+--+--+
  | tbl_workermap | 0 | PRIMARY | 1 | workermap_id | A | 7184 | NULL | NULL |  | BTREE |  |  |
  +---------------+---+---------+---+--------------+---+------+------+------+--+-------+--+--+
  </code></pre>
  
  <p>EXPLAIN query</p>
  
  <pre><code>+---+--------+----------------+--------+---------+---------+------+-------------------------------+------+----------------------------------------------------+
  | 1 | SIMPLE |      job       |  ALL   |  NULL   |  NULL   | NULL |             NULL              | 8283 |    Using where; Using temporary; Using filesort    |
  +---+--------+----------------+--------+---------+---------+------+-------------------------------+------+----------------------------------------------------+
  | 1 | SIMPLE | estimategroup  | eq_ref | PRIMARY | PRIMARY | 4    | jobq.job.estimategroup_id     |    1 | Using where                                        |
  | 1 | SIMPLE | brand          | eq_ref | PRIMARY | PRIMARY | 4    | jobq.job.brand_id             |    1 | Using index condition                              |
  | 1 | SIMPLE | site           | eq_ref | PRIMARY | PRIMARY | 4    | jobq.job.site_id              |    1 | Using where                                        |
  | 1 | SIMPLE | addedby        | eq_ref | PRIMARY | PRIMARY | 4    | jobq.job.addedby_user_id      |    1 | Using index condition                              |
  | 1 | SIMPLE | requester      | eq_ref | PRIMARY | PRIMARY | 4    | jobq.job.requester_user_id    |    1 | Using index condition                              |
  | 1 | SIMPLE | worker         | eq_ref | PRIMARY | PRIMARY | 4    | jobq.job.worker_user_id       |    1 | Using index condition                              |
  | 1 | SIMPLE | status         | ALL    | PRIMARY | NULL    | NULL | NULL                          |    6 | Using where; Using join buffer (Block Nested Loop) |
  | 1 | SIMPLE | workermap      | ALL    | NULL    | NULL    | NULL | NULL                          | 7184 | Using where; Using join buffer (Block Nested Loop) |
  | 1 | SIMPLE | user_workermap | eq_ref | PRIMARY | PRIMARY | 4    | jobq.workermap.worker_user_id |    1 | Using where                                        |
  | 1 | SIMPLE | categorymap    | ALL    | NULL    | NULL    | NULL | NULL                          |    1 | Using where; Using join buffer (Block Nested Loop) |
  | 1 | SIMPLE | category       | eq_ref | PRIMARY | PRIMARY | 4    | jobq.categorymap.category_id  |    1 | Using where                                        |
  +---+--------+----------------+--------+---------+---------+------+-------------------------------+------+----------------------------------------------------+
  
  </code></pre>