SELECT WHERE IN(子查询)慢

2022-01-23 00:00:00 performance where-clause select subquery mysql

我已经尝试了解决方案 这里，但它不起作用.

I've tried the solution here, but it doesn't work.

我的桌子是这样的:

   `Index`  uid   dept
...........................
      1    001   dept1
      2    001   dept2
      3    001   dept3
      4    002   dept2
      5    002   dept3
      6    002   dept4
      7    003   dept1
      8    003   dept5
      9    004   dept1
      10   004   dept6

我想检索具有特定 dept 的所有行.也就是说，如果我想检索 dept1，我想检索除 uid=002 之外的所有行，因为 uid=002 没有 dept1.

I want to retrieve all the rows with a particular dept. That is, If I want to retrieve dept1, I want to retrieve all rows except uid=002, since there's no dept1 for uid=002.

即使使用索引，查询字符串也很慢:

The query string is slow even when using index:

SELECT id FROM table WHERE uid IN
(SELECT uid WHERE dept='dept1')

我之前没有使用 WHERE IN 的版本如下:

My previous version without using WHERE IN is as following:

首先检索所有带有 dept=dept1 的 uid.
然后对第一个查询中检索到的所有 uid 使用 for 循环.

Retrieves all the uid with dept=dept1 first.
Then use a for-loop for all uid retrieved in the first query.

对于在第一个查询中检索到的少量 (100) 行，此方法非常快.但是，这似乎不是一个好的解决方案，因为它会创建大量查询(每个查询都非常快).

This method is very fast for a small amount(100) of rows retrieved in the first query. However, it seems that it's not a good solution because it creates a lot of queries(each of them is extremely fast).

推荐答案

试试这个:

select a.id from Table1 a
inner join Table1 b on a.uid = b.uid and b.dept = 'dept1';

演示:http://sqlfiddle.com/#!2/05774/4

相关文章