title: SQL: How to speed up a query using indexing
body: <p>I am trying to speed up a query to find all CUSTOMERs who have bought a MOTORCYCLE manufactured before 1970 AND bought another MOTORCYCLE manufactured after 2010. Since my query is running very slowly, I think that I need help with finding the better indexes. My attempts are documented below:</p>
  
  <p><strong>Tables</strong></p>
  
  <pre><code>CREATE TABLE CUSTOMER (
    id int PRIMARY KEY, 
    fname varchar(30),     
    lname varchar(30)
  );
  
  CREATE TABLE MOTORCYCLE (
    id int PRIMARY KEY, 
    name varchar(30), 
    year int -- Manufactured year
  );
  
  CREATE TABLE SALES (
    cid int,
    mid int,
    FOREIGN KEY(cid) REFERENCES CUSTOMER(id), 
    FOREIGN KEY(mid) REFERENCES MOTOCYCLE(id),
    PRIMARY KEY(pid, mid, role)
  );
  </code></pre>
  
  <p><strong>Indexes</strong></p>
  
  <p>Here are my indexes (I am somewhat guessing with these, but this was my attempt):</p>
  
  <pre><code>CREATE UNIQUE INDEX customerID on CUSTOMER(id);
  CREATE INDEX customerName on CUSTOMER(fname, lname);
  
  CREATE UNIQUE INDEX motorcycleID on MOTORCYCLE(id);
  CREATE INDEX motorcycleName on MOTORCYCLE(name);
  CREATE INDEX motorcycleYear on MOTORCYCLE(year);
  
  CREATE INDEX salesCustomerMotorcycleID on SALES(cid, mid);       
  CREATE INDEX salesCustomerID on SALES(cid);
  CREATE INDEX castsMotorcycleID on SALES(mid);
  </code></pre>
  
  <p><strong>Queries</strong></p>
  
  <p>My query to find the customers purchasing bikes manufactured before 1970 and after 2010 is here:</p>
  
  <pre><code>SELECT fname, lname
  FROM (SALES INNER JOIN CUSTOMER ON SALES.cid=CUSTOMER.id) INNER JOIN MOTORCYCLE ON MOTORCYCLE.id=SALES.mid
  GROUP BY CUSTOMER.id
  HAVING MIN(MOTORCYCLE.year) &lt; 1970 AND MAX(MOTORCYCLE.year) &gt; 2010;
  </code></pre>
  
  <p>And here is another working query which avoids the <code>GROUP BY</code> and <code>HAVING</code> clauses:</p>
  
  <pre><code>SELECT DISTINCT C.id, fname, lname
  FROM (CUSTOMER as C inner join (SALES as S1 INNER JOIN MOTORCYCLE as M1 ON M1.id=S1.mid) on C.id=S1.cid) inner join (SALES as S2 inner join MOTORCYCLE as M2 on S2.mid=M2.id) on C.id=S2.cid
  WHERE (M1.year &lt; 1970 AND M2.year &gt; 2010);
  </code></pre>
  
  <p>Any suggestions on the kinds of indexes I can use to speed up my query? Or should I change my query?</p>
  
  <p><strong>UPDATE</strong> </p>
  
  <p>I found another query that also works, but it is also too slow. It has been added above. Still, it might be helpful when finding an index to speed it up.</p>