From: Thimble Smith
Date: March 24 1999 5:28am
Subject: LEFT JOIN tbl USING (pkey_a = 'constant' AND m_key = ref)
List-Archive: http://lists.mysql.com/mysql/860
Message-Id: <19990323222829.P29801@desert.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Hi.  I've got two queries that return identical results and show
very similar results under EXPLAIN, but one takes 0.02 seconds
and the other takes 4.5 seconds.

It's odd that, for the fast query, EXPLAIN reports more rows are
needed.

I don't really need help with anything, I'm just curious about
why this happens.  Perhaps the slow version could be translated
into the fast version in the optimizer?  Thanks for any input.

Tim

  mysql> explain select count(*)
      -> from a left join b on (b.a_id = a.id)
      -> where b.type = 'X' OR b.type IS NULL;
  +-------+-------+---------------+---------+----+------+------+-----------+
  | table | 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 |   54 |where used |
  +-------+-------+---------------+---------+----+------+------+-----------+
  2 rows in set (0.01 sec)

  mysql> select count(*)
      -> from a left join b on (b.a_id = a.id)
      -> where b.type = 'X' OR b.type IS NULL;
  +----------+
  | count(*) |
  +----------+
  |       44 |
  +----------+
  1 row in set (0.02 sec)

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

  mysql> select count(*)
      -> from a left join b on (b.type = 'X' and b.a_id = a.id);
  +----------+
  | count(*) |
  +----------+
  |       44 |
  +----------+
  1 row in set (4.52 sec)

  #
  # Table structure for table 'a'
  #
  CREATE TABLE a (
    id smallint(5) unsigned DEFAULT '0' NOT NULL auto_increment,
    foo mediumint(8) unsigned DEFAULT '0' NOT NULL,
    PRIMARY KEY (id),
    KEY foo_k (foo)
  );

  #
  # Table structure for table 'b'
  #
  CREATE TABLE b (
    type char(10) DEFAULT '' NOT NULL,
    num mediumint(8) unsigned DEFAULT '0' NOT NULL,
    a_id smallint(5) unsigned DEFAULT '0' NOT NULL,
    KEY a_id_k (a_id),
    PRIMARY KEY (type,num)
  );

  $ mysqladmin version 
  mysqladmin  Ver 7.8 Distrib 3.22.14b-gamma, for unknown-freebsd2.2.7 on i386
  TCX Datakonsult AB, by Monty

  Server version          3.22.14b-gamma
  Protocol version        10
  Connection              Localhost via UNIX socket
  UNIX socket             /usr/local/mysql/var/mysql.sock
  Uptime:                 60 days 5 hours 54 min 40 sec

  Threads: 2  Questions: 79128  Slow queries: 6  Opens: 1374  Flush tables: 2
  Open tables: 63