title: Rewriting mysql select to reduce time and writing tmp to disk
body: <p>I have a mysql query that's taking several minutes which isn't very good as it's used to create a web page.</p>
  
  <p>Three tables are used: poster_data contains information on individual posters. poster_categories lists all the categories (movies, art, etc) while poster_prodcat lists the posterid number and the categories it can be in e.g. one poster would have multiple lines for say, movies, indiana jones, harrison ford, adventure films, etc. </p>
  
  <p>this is the slow query: </p>
  
  <pre><code>select * 
    from poster_prodcat, 
         poster_data, 
         poster_categories 
   where poster_data.apnumber = poster_prodcat.apnumber 
     and poster_categories.apcatnum = poster_prodcat.apcatnum 
     and poster_prodcat.apcatnum='623'  
  ORDER BY aptitle ASC 
     LIMIT 0, 32
  </code></pre>
  
  <p>According to the explain:</p>
  
  <p><img src="https://i.stack.imgur.com/pujUi.jpg" alt="explain"></p>
  
  <p>It was taking a few minutes. Poster_data has just over 800,000 rows, while poster_prodcat has just over 17 million. Other category queries with this select are barely noticeable, while poster_prodcat.apcatnum='623' has about 400,000 results and is writing out to disk</p>