>>>>> "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
