From: Thimble Smith
Date: March 24 1999 1:26pm
Subject: Re: LEFT JOIN tbl USING (pkey_a = 'constant' AND m_key = ref)
List-Archive: http://lists.mysql.com/mysql/888
Message-Id: <19990324062657.C29801@desert.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

On Wed, Mar 24, 1999 at 02:55:40PM +0200, Michael Widenius wrote:
> How many rows is there in table 'a' ?

There are 44 rows in table a, and 1283 rows in b.  803 rows in b have
type 'X'.

> The problem is probably that MySQL 'guess' wrong in that there is only 
> 12 rows that matches 'X' in the second query and thus uses the wrong
> key.

That was it.  Thanks!

Tim

After running isamchk -a, both queries happen very fast, and
explain says:

  mysql> explain select count(*)
      -> from a left join b on (b.a_id = a.id)
          -> where (b.type = 'X' OR b.type IS NULL);
  +---+-------+---------------+---------+---+------+------+-------------+
  |tbl| type  | possible_keys | key     |kl | ref  | rows | Extra       |
  +---+-------+---------------+---------+---+------+------+-------------+
  | a | index | NULL          | PRIMARY | 2 | NULL |   44 | Using index |
  | b | ref   | a_id_k        | a_id_k  | 2 | a.id |    2 | where used  |
  +---+-------+---------------+---------+---+------+------+-------------+
  2 rows in set (0.00 sec)

  mysql> explain select count(*)
      -> from a left join b on (b.type = 'X' and b.a_id = a.id);
  +---+-------+----------------+---------+---+------+------+-------------+
  |tbl| type  | possible_keys  | key     |kl | ref  | rows | Extra       |
  +---+-------+----------------+---------+---+------+------+-------------+
  | a | index | NULL           | PRIMARY | 2 | NULL |   44 | Using index |
  | b | ref   | a_id_k,PRIMARY | a_id_k  | 2 | a.id |    2 |             |
  +---+-------+----------------+---------+---+------+------+-------------+
  2 rows in set (0.01 sec)