类似如:
SELECT * FROM Table1
WHERE userid IN
(
SELECT userid FROM Table2 WHERE tid=33
)这样的语句在MySQL里效率很低,查询时间很长,不知道有好的解决方法没?
SELECT * FROM Table1
WHERE userid IN
(
SELECT userid FROM Table2 WHERE tid=33
)这样的语句在MySQL里效率很低,查询时间很长,不知道有好的解决方法没?
inner join Table2 b on a.userid=b.userid
WHERE b.tid=33
在 sqlyog里面执行需要将近1分钟多,数据很少的不超过2W条开发公司当初设计的表不怎么合理,但是我现在查的2个表似乎还行,没不合理之处,索引都有
-> SUM(CASE WHEN state>=1 THEN 1 ELSE 0 END) AS HasComment
-> FROM tbl_homework AS A
-> INNER JOIN tbl_teacher B ON A.user_id=B.user_id
-> WHERE B.teacher_id=33
-> GROUP BY B.teacher_id;
+------------+------------+
| teacher_id | HasComment |
+------------+------------+
| 33 | 161 |
+------------+------------+
1 row in set (2 min 49.39 sec)
SUM(CASE WHEN state>=1 THEN 1 ELSE 0 END) AS HasComment
FROM tbl_homework AS A
INNER JOIN tbl_teacher B ON A.user_id=B.user_id
WHERE B.teacher_id=33
GROUP BY B.teacher_id;
SUM(CASE WHEN state>=1 THEN 1 ELSE 0 END) AS HasComment
FROM tbl_homework
WHERE user_id IN
(
select user_id from tbl_teacher where teacher_id=33
)
14秒不用 group by,只统计数量。
------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len
ref | rows | Extra |
+----+-------------+-------+------+--------------------+------------+---------
------+-------+-------------+
| 1 | SIMPLE | B | ref | user_id,teacher_id | teacher_id | 8
const | 470 | Using index |
| 1 | SIMPLE | A | ALL | NULL | NULL | NULL
NULL | 38548 | Using where |
+----+-------------+-------+------+--------------------+------------+---------
------+-------+-------------+
2 rows in set (0.00 sec)
SELECT
SUM(CASE WHEN state>=1 THEN 1 ELSE 0 END) AS HasComment
FROM tbl_homework a inner join tbl_teacher b on a.user_id=b.user_id
where b.teacher_id=33or
SELECT
SUM(CASE WHEN state>=1 THEN 1 ELSE 0 END) AS HasComment
FROM tbl_homework a inner join tbl_teacher b on a.user_id=b.user_id and b.teacher_id=33要多长时间?
-> SUM(CASE WHEN state>=1 THEN 1 ELSE 0 END) AS HasComment
-> FROM tbl_homework a inner join tbl_teacher b on a.user_id=b.user_id
-> where b.teacher_id=33;
+------------+
| HasComment |
+------------+
| 161 |
+------------+
1 row in set (6.08 sec)mysql> SELECT
-> SUM(CASE WHEN state>=1 THEN 1 ELSE 0 END) AS HasComment
-> FROM tbl_homework a inner join tbl_teacher b on a.user_id=b.user_id and
.teacher_id=33;
+------------+
| HasComment |
+------------+
| 161 |
+------------+
1 row in set (4.75 sec)