0

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

My table is like this:

   `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

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')

My previous version without using WHERE IN is as following:

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

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).

Community
  • 1
  • 1
benck
  • 2,034
  • 1
  • 22
  • 31

1 Answers1

8

Try this one:

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

Demo: http://sqlfiddle.com/#!2/05774/4

Sergio Tulentsev
  • 226,338
  • 43
  • 373
  • 367
  • [When is `IN (SELECT ...)` performance going to be improved?](http://datacharmer.blogspot.com/2008/09/drizzling-mysql.html) – ypercubeᵀᴹ Apr 05 '12 at 06:14
  • 1
    Good news: [MariaDB 5.3](http://kb.askmonty.org/en/what-is-mariadb-53) is already out as a stable release. And MySQL 5.6 (not stable yet, but soon I think) will have these or similar improvements. – ypercubeᵀᴹ Apr 05 '12 at 06:16
  • 1
    This query is equivalent to the subquery version. I'm glad that mysql improve the performance in newer version. However, the stable version for debian squeeze is still 5.1. – benck Apr 06 '12 at 05:35