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
