Two small corrections:
ad point 1.
"b in ('2004-05-01')" behaves different from "b = '2004-05-01'". The
last one is better: key_len = 6, instead of 3.
mysql> explain select count(*) from A, B where A.a=B.a and b ='2004-05-01';
+-------+-------+---------------+---------+---------+-----------+------+--------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+-------+---------------+---------+---------+-----------+------+--------------------------+
| B | index | PRIMARY | PRIMARY | 3 | NULL | 63 | Using index
|
| A | ref | a | a | 6 | B.a,const | 1 | Using where;
Using index |
+-------+-------+---------------+---------+---------+-----------+------+--------------------------+
2 rows in set (0.00 sec)
mysql> explain select count(*) from A, B where A.a=B.a and b in('2004-05-01');
+-------+-------+---------------+---------+---------+------+------+--------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+-------+---------------+---------+---------+------+------+--------------------------+
| B | index | PRIMARY | PRIMARY | 3 | NULL | 63 | Using index
|
| A | ref | a | a | 3 | B.a | 182 | Using where; Using
index |
+-------+-------+---------------+---------+---------+------+------+--------------------------+
2 rows in set (0.00 sec)
ad point 2.
deleting rows can help, but the case I saw had 113 rows in B, not 10.
Then explain says it will use key_len=6 but I don't think it will use
the index at all, since number of rows in explain output is same as in
the whole table...
mysql> explain select count(*) from A, B where A.a=B.a and (b ='2004-05-01' or
b='2004-05-02');
+-------+--------+---------------+---------+---------+------+------+--------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+--------+---------------+---------+---------+------+------+--------------------------+
| A | index | a | a | 6 | NULL | 2545 | Using where;
Using index |
| B | eq_ref | PRIMARY | PRIMARY | 3 | A.a | 1 | Using index
|
+-------+--------+---------------+---------+---------+------+------+--------------------------+
2 rows in set (0.00 sec)
mysql> select count(*) from A;
+----------+
| count(*) |
+----------+
| 2545 |
+----------+
1 row in set (0.01 sec)
mysql> select count(*) from B;
+----------+
| count(*) |
+----------+
| 113 |
+----------+
1 row in set (0.00 sec)
Hey! Some good news: I think I have found a work around: put the days
also in a (maybe temporary) table and the full key is used:
create table C (b date not null primary key);
insert into C values ('2004-05-01', '2004-05-02');
explain select count(*) from A, B, C where A.a=B.a and A.b=C.b ;
+-------+-------+---------------+---------+---------+---------+------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+---------+---------+---------+------+-------------+
| B | index | PRIMARY | PRIMARY | 3 | NULL | 63 | Using index |
| C | index | PRIMARY | PRIMARY | 3 | NULL | 2 | Using index |
| A | ref | a | a | 6 | B.a,C.b | 1 | Using index |
+-------+-------+---------------+---------+---------+---------+------+-------------+
3 rows in set (0.01 sec)
It looks silly to me, but is happens to work.
Herald
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
