title: how can I make this query more efficient?
body: <p>edit: here is a simplified version of the original query (runs in 3.6 secs on a products table of 475K rows)</p>
  
  <pre><code>SELECT p.*, shop FROM products p JOIN
  users u ON p.date &gt;= u.prior_login and u.user_id = 22 JOIN
  shops s ON p.shop_id = s.shop_id
  ORDER BY shop, date, product_id;
  </code></pre>
  
  <p>this is the explain plan</p>
  
  <pre><code>id  select_type table   type    possible_keys   key key_len ref rows    Extra
  1   SIMPLE  u   const   PRIMARY,prior_login,user_id PRIMARY 4   const   1   Using temporary; Using filesort
  1   SIMPLE  s   ALL PRIMARY NULL    NULL    NULL    90   
  1   SIMPLE  p   ref shop_id,date,shop_id_2,shop_id_3    shop_id 4   bitt3n_minxa.s.shop_id  5338    Using where
  </code></pre>
  
  <p>the bottleneck seems to be <code>ORDER BY date,product_id</code>. Removing these two orderings, the query runs in 0.06 seconds. (Removing either one of the two (but not both) has virtually no effect, query still takes over 3 seconds.) I have indexes on both product_id and date in the products table. I have also added an index on (product,date) with no improvement.</p>
  
  <p>newtover suggests the problem is the fact that the <code>INNER JOIN users u1 ON products.date &gt;= u1.prior_login</code> requirement is preventing use of the index on products.date</p>
  
  <p>Two variations of the query that execute in ~0.006 secs (as opposed to 3.6 secs for the original) have been suggested to me (not from this thread).</p>
  
  <p>this one uses a subquery, which appears to force the order of the joins</p>
  
  <pre><code>SELECT p.*, shop 
    FROM 
    (
      SELECT p.*
      FROM products p 
      WHERE p.date &gt;= (select prior_login FROM users where user_id = 22)
    ) as p
    JOIN shops s 
      ON p.shop_id = s.shop_id
    ORDER BY shop, date, product_id;
  </code></pre>
  
  <p>this one uses the WHERE clause to do the same thing (although the presence of SQL_SMALL_RESULT doesn't change the execution time, 0.006 secs without it as well)</p>
  
  <pre><code>SELECT SQL_SMALL_RESULT p . * , shop
  FROM products p
  INNER JOIN shops s ON p.shop_id = s.shop_id
  WHERE p.date &gt;= ( 
  SELECT prior_login
  FROM users
  WHERE user_id =22 ) 
  ORDER BY shop, DATE, product_id;
  </code></pre>
  
  <p>My understanding is that these queries work much faster on account of reducing the relevant number of rows of the product table before joining it to the shops table. I am wondering if this is correct.</p>