title: How do I rewrite this MySQL query to use less memory?
body: <p>I was practicing writing mysql queries with a sample mysql database that has 500k entries in employees and and salaries (see tables below). I wanted a list of managers who are not in finance but make more money than at least one person in finance. (dept_no=<code>d002</code>)</p>
  
  <p>query:</p>
  
  <pre><code>SELECT salaries.salary, employees.first_name, employees.last_name 
  FROM salaries, employees, dept_manager, dept_emp
  WHERE employees.emp_no=dept_manager.emp_no
  AND dept_manager.dept_no &lt;&gt; "d002"
  AND salaries.emp_no = dept_manager.emp_no
  AND salaries.salary &gt; ANY 
      (SELECT salary FROM salaries, dept_emp 
          WHERE dept_emp.dept_no="d002" 
          AND salaries.emp_no = dept_emp.emp_no); 
  </code></pre>
  
  <p>This query crashes the sql server because it runs out of memory before it can complete. This is on a macbook 10.6 2.2GB core duo and 2Gb RAM. Granted this may be asking a lot but I feel like there should be a way of doing this with somewhat limited computing power. I've done other queries without crashing (they were pretty slow but still)</p>
  
  <p>error message for the curious:</p>
  
  <pre><code>mysql(611) malloc: *** mmap(size=3870720) failed (error code=12)
  *** error: can't allocate region
  *** set a breakpoint in malloc_error_break to debug
  mysql: Out of memory (Needed 3869736 bytes)
  ERROR 2008 (HY000): MySQL client ran out of memory
  </code></pre>
  
  <p>table:</p>
  
  <pre><code>CREATE TABLE employees (
      emp_no      INT             NOT NULL,
      birth_date  DATE            NOT NULL,
      first_name  VARCHAR(14)     NOT NULL,
      last_name   VARCHAR(16)     NOT NULL,
      gender      ENUM ('M','F')  NOT NULL,    
      hire_date   DATE            NOT NULL,
      PRIMARY KEY (emp_no)
  );
  
  CREATE TABLE departments (
      dept_no     CHAR(4)         NOT NULL,
      dept_name   VARCHAR(40)     NOT NULL,
      PRIMARY KEY (dept_no),
      UNIQUE  KEY (dept_name)
  );
  
  CREATE TABLE dept_manager (
     dept_no      CHAR(4)         NOT NULL,
     emp_no       INT             NOT NULL,
     from_date    DATE            NOT NULL,
     to_date      DATE            NOT NULL,
     KEY         (emp_no),
     KEY         (dept_no),
     FOREIGN KEY (emp_no)  REFERENCES employees (emp_no)    ON DELETE CASCADE,
     FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
     PRIMARY KEY (emp_no,dept_no)
  ); 
  
  CREATE TABLE dept_emp (
      emp_no      INT             NOT NULL,
      dept_no     CHAR(4)         NOT NULL,
      from_date   DATE            NOT NULL,
      to_date     DATE            NOT NULL,
      KEY         (emp_no),
      KEY         (dept_no),
      FOREIGN KEY (emp_no)  REFERENCES employees   (emp_no)  ON DELETE CASCADE,
      FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE,
      PRIMARY KEY (emp_no,dept_no)
  );
  
  CREATE TABLE titles (
      emp_no      INT             NOT NULL,
      title       VARCHAR(50)     NOT NULL,
      from_date   DATE            NOT NULL,
      to_date     DATE,
      KEY         (emp_no),
      FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
      PRIMARY KEY (emp_no,title, from_date)
  ); 
  
  CREATE TABLE salaries (
      emp_no      INT             NOT NULL,
      salary      INT             NOT NULL,
      from_date   DATE            NOT NULL,
      to_date     DATE            NOT NULL,
      KEY         (emp_no),
      FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
      PRIMARY KEY (emp_no, from_date)
  ); 
  </code></pre>
  
  <p>No I didn't write this it was available from a third party site linked from the MySQL website.</p>