TEST1和TEST2都是千万级的表
下面这个sql要执行2分多钟
SELECT MIN(CV),MAX(CV),COUNT(CV) FROM TEST1 A INNER JOIN ( select C1 FROM TEST2 where C2 = 2010 AND C3 = 4 )B ON A.C1 = B.C1;
下面的sql执行等了半天不出结果
SELECT MIN(CV),MAX(CV),COUNT(CV) FROM TEST1 A INNER JOIN TEST2 B ON B.C2 = 2010 AND B.C3 = 4 AND A.C1 = B.C1;求助!求助!
下面这个sql要执行2分多钟
SELECT MIN(CV),MAX(CV),COUNT(CV) FROM TEST1 A INNER JOIN ( select C1 FROM TEST2 where C2 = 2010 AND C3 = 4 )B ON A.C1 = B.C1;
下面的sql执行等了半天不出结果
SELECT MIN(CV),MAX(CV),COUNT(CV) FROM TEST1 A INNER JOIN TEST2 B ON B.C2 = 2010 AND B.C3 = 4 AND A.C1 = B.C1;求助!求助!
B: C1C2C3 C2C3
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+------------------+--------+---------+------+---------+--------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 72457 | |
| 1 | PRIMARY | A | ref | C1,C1C3 | C1 | 5 | B.C1 | 307 | Using where |
| 2 | DERIVED | TEST2 | index | C2,C2C5C3C1,C2C3 | C1C2C3 | 141 | NULL | 1974366 | Using where; Using index
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------------------+------+---------+-------------+----------+-------------+
| 1 | SIMPLE | A | ALL | C1,C1C3 | NULL | NULL | NULL | 25173732 | |
| 1 | SIMPLE | B | ref | C1,C2,C2C5C3C1,C1C2C3,C2C3 | C1 | 5 | A.C1 | 1 | Using where
从执行的计划看c2和c3列为加索引。
这样的语句不适合用关系数据库做统计 用infobright