From: Michael Widenius
Date: March 24 1999 12:55pm
Subject: LEFT JOIN tbl USING (pkey_a = 'constant' AND m_key = ref)
List-Archive: http://lists.mysql.com/mysql/876
Message-Id: <14072.57125.647322.713920@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


>>>>> "Thimble" == Thimble Smith <tim@stripped> writes:

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

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

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

Thimble> 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;
Thimble>   +-------+-------+---------------+---------+----+------+------+-----------+
Thimble>   | table | type  | possible_keys | key     | kl | ref  | rows |Extra      |
Thimble>   +-------+-------+---------------+---------+----+------+------+-----------+
Thimble>   | a     | index | NULL          | PRIMARY |  2 | NULL |   44 |Using index|
Thimble>   | b     | ref   | a_id_k        | a_id_k  |  2 | a.id |   54 |where used |
Thimble>   +-------+-------+---------------+---------+----+------+------+-----------+
Thimble>   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;
Thimble>   +----------+
Thimble>   | count(*) |
Thimble>   +----------+
Thimble>   |       44 |
Thimble>   +----------+
Thimble>   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);
Thimble>   +-------+-------+----------------+---------+----+------+------+----------+
Thimble>   | table | type  | possible_keys  | key     | kl | ref  | rows |Extra     |
Thimble>   +-------+-------+----------------+---------+----+------+------+----------+
Thimble>   | a     | index | NULL           | PRIMARY |  2 | NULL |   44 |Usingindex|
Thimble>   | b     | ref   | a_id_k,PRIMARY | PRIMARY | 10 | X    |   12 |          |
Thimble>   +-------+-------+----------------+---------+----+------+------+----------+
Thimble>   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);
Thimble>   +----------+
Thimble>   | count(*) |
Thimble>   +----------+
Thimble>   |       44 |
Thimble>   +----------+
Thimble>   1 row in set (4.52 sec)

Hi!

How many rows is there in table 'a' ?

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.

Can you check how many rows there is with 'X' in 'b' ?

You may give MySQL more information of how your index are used by
running:

isamchk -a */*.ISM
mysqladmin flush-tables

Can you do this and try the later query again?

Regards,
Monty