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)
