title: Why is my query so slow? Trying to find the null fields on a left join in mysql
body: <p>EDIT: Updated with suggestions from Bill Karwin below.  Still very slow.</p>
  
  <p>I'm trying to write a query that will find all items on an order that are entered to a warehouse that doesn't have a record for that item in that warehouse.  As an example, if item XYZ is entered for warehouse A, but warehouse A doesn't actually carry item XYZ, I want the order item to show up in my report.</p>
  
  <p>I'm able to run the query just fine, but it seems to take forever (50 seconds).  It seems to be hanging mainly on the "is null" condition I have in the where clause. If I remove the condition with the "is null" and run it, it executes in about 4.8s.  Here's my query:</p>
  
  <pre><code>SELECT
  saw_order.Wo,
  saw_orderitem.Item,
  saw_orderitem.Stock,
  saw_order.`Status`,
  saw_order.`Date`,
  saw_orderitem.Warehouse,
  saw_stockbalance.Balno,
  saw_stockbalance.Stock
  FROM
  saw_order
  Inner Join saw_orderitem ON saw_order.Wo = saw_orderitem.Wo
  Inner Join saw_stock ON saw_orderitem.Stock = saw_stock.Stock
  Left Join saw_stockbalance ON saw_orderitem.Stock = saw_stockbalance.Stock 
      AND saw_orderitem.Warehouse = saw_stockbalance.Warehouse
  WHERE
  saw_order.`Status` Between 3 and 81 and
  saw_stockbalance.Stock Is Null
  </code></pre>
  
  <p>When I <code>explain</code> the query above, I see:</p>
  
  <pre><code>+----+-------------+------------------+--------+------------------------------+---------+---------+-------------------------------------------------------+-------+-------------------------+
  | id | select_type | table            | type   | possible_keys                | key     | key_len | ref                                                   | rows  | Extra                   |
  +----+-------------+------------------+--------+------------------------------+---------+---------+-------------------------------------------------------+-------+-------------------------+
  |  1 | SIMPLE      | saw_stock        | index  | PRIMARY                      | PRIMARY | 17      | NULL                                                  | 32793 | Using index             |
  |  1 | SIMPLE      | saw_orderitem    | ref    | PRIMARY,Stock,StockWarehouse | Stock   | 17      | saws.saw_stock.Stock                                  |    68 |                         |
  |  1 | SIMPLE      | saw_order        | eq_ref | PRIMARY,Status               | PRIMARY | 4       | saws.saw_orderitem.Wo                                 |     1 | Using where             |
  |  1 | SIMPLE      | saw_stockbalance | ref    | Stock,Warehouse              | Stock   | 20      | saws.saw_orderitem.Stock,saws.saw_orderitem.Warehouse |     1 | Using where; Not exists |
  +----+-------------+------------------+--------+------------------------------+---------+---------+-------------------------------------------------------+-------+-------------------------+
  </code></pre>
  
  <p>I'm pretty sure I have indexes for all of the fields of the respective tables in my joins, but can't figure out how to rewrite the query to make it go faster.</p>
  
  <p>EDIT: Here are the indexes I have set up on my tables:</p>
  
  <pre><code>mysql&gt; show index from saw_order;
  +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  | Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
  +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  | saw_order |          0 | PRIMARY  |            1 | Wo          | A         |      553425 | NULL     | NULL   |      | BTREE      |         |
  | saw_order |          1 | Customer |            1 | Customer    | A         |       14957 | NULL     | NULL   |      | BTREE      |         |
  | saw_order |          1 | Other    |            1 | Other       | A         |         218 | NULL     | NULL   |      | BTREE      |         |
  | saw_order |          1 | Site     |            1 | Site        | A         |           8 | NULL     | NULL   |      | BTREE      |         |
  | saw_order |          1 | Date     |            1 | Date        | A         |        1594 | NULL     | NULL   |      | BTREE      |         |
  | saw_order |          1 | Status   |            1 | Status      | A         |          15 | NULL     | NULL   |      | BTREE      |         |
  +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  6 rows in set
  
  mysql&gt; show index from saw_orderitem;
  +---------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  | Table         | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
  +---------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  | saw_orderitem |          0 | PRIMARY        |            1 | Wo          | A         | NULL        | NULL     | NULL   |      | BTREE      |         |
  | saw_orderitem |          0 | PRIMARY        |            2 | Item        | A         |     1842359 | NULL     | NULL   |      | BTREE      |         |
  | saw_orderitem |          1 | Stock          |            1 | Stock       | A         |       27093 | NULL     | NULL   |      | BTREE      |         |
  | saw_orderitem |          1 | Product        |            1 | Product     | A         |         803 | NULL     | NULL   |      | BTREE      |         |
  | saw_orderitem |          1 | GGroup         |            1 | GGroup      | A         |         114 | NULL     | NULL   |      | BTREE      |         |
  | saw_orderitem |          1 | ShipVia        |            1 | ShipVia     | A         |         218 | NULL     | NULL   |      | BTREE      |         |
  | saw_orderitem |          1 | Warehouse      |            1 | Warehouse   | A         |           9 | NULL     | NULL   |      | BTREE      |         |
  | saw_orderitem |          1 | StockWarehouse |            1 | Stock       | A         |       27093 | NULL     | NULL   |      | BTREE      |         |
  | saw_orderitem |          1 | StockWarehouse |            2 | Warehouse   | A         |       49793 | NULL     | NULL   |      | BTREE      |         |
  +---------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  9 rows in set
  
  mysql&gt; show index from saw_stock;
  +-----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
  | Table     | Non_unique | Key_name          | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
  +-----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
  | saw_stock |          0 | PRIMARY           |            1 | Stock             | A         |       32793 | NULL     | NULL   |      | BTREE      |         |
  | saw_stock |          1 | Class             |            1 | Class             | A         |         655 | NULL     | NULL   | YES  | BTREE      |         |
  | saw_stock |          1 | DateFirstReceived |            1 | DateFirstReceived | A         |        2732 | NULL     | NULL   |      | BTREE      |         |
  +-----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
  3 rows in set
  
  mysql&gt; show index from saw_stockbalance;
  +------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  | Table            | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
  +------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  | saw_stockbalance |          0 | PRIMARY   |            1 | Balno       | A         |      146315 | NULL     | NULL   |      | BTREE      |         |
  | saw_stockbalance |          1 | Stock     |            1 | Stock       | A         |       36578 | NULL     | NULL   |      | BTREE      |         |
  | saw_stockbalance |          1 | Stock     |            2 | Warehouse   | A         |      146315 | NULL     | NULL   |      | BTREE      |         |
  | saw_stockbalance |          1 | Warehouse |            1 | Warehouse   | A         |          11 | NULL     | NULL   |      | BTREE      |         |
  +------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
  4 rows in set
  </code></pre>
  
  <p>Any ideas?</p>