title: MySQL select query with join and a lot of results optimization
body: <p>I have two tables. The first one contains all the item informations and the second contains the item ID with a category ID. The reason of that is because an item can be in more than one category. I have about 500 000 items in my table.</p>
  
  <p>Here is a select query exemple:</p>
  
  <pre><code>SELECT SQL_CALC_FOUND_ROWS items.* 
  FROM items 
      INNER JOIN cat
          ON items.iid=cat.iid 
  WHERE (items.expire&gt;'1308061323' AND cat.cid = '1') 
  AND (items.code=71 OR items.code=23) 
  ORDER BY price DESC 
  LIMIT 0, 50
  </code></pre>
  
  <ul>
  <li>iid = item ID</li>
  <li>cid = category ID</li>
  <li>code = A code I use for search only.</li>
  <li>expire = The expiration time for an
  item</li>
  </ul>
  
  <p>I use SQL_CALC_FOUND_ROWS because I want to display the total matching results. I display only 50 items on the page (LIMIT 0, 50).</p>
  
  <p>When I execute that query, my php page take about 5 seconds to load (less than 1 without the query). </p>
  
  <ul>
  <li>Is there a way to optimize it?</li>
  <li>Is it faster to use SQL_CALC_FOUND_ROWS or a second query with SELECT COUNT(*)?</li>
  <li>I heard about indexes, but I don't know how to use them and what they do. Can they help?</li>
  </ul>