title: How to speed up a "max()-searching" SQL Subquery
body: <p>I have created this good but slow working SQL-Statement to search the highest price from a pricelist linked to an item from the itemlist. Known is the items itemnr:</p>
  
  <pre><code>SELECT i.ItemNr, i.ItemId, x.maxprice
    FROM itemlist i,
         pricelist p,
         (SELECT MAX (p2.price) AS maxprice, p2.ItemId
            FROM pricelist p2
           GROUP BY p2.ItemId) x
   WHERE i.ItemNr = 4711
     AND i.ItemId = p.ItemId
     AND i.ItemId = x.ItemId
     AND p.price = x.maxprice
  </code></pre>
  
  <p>The itemlist holds about 100000 items and the pricelist about 1000000 prices. This statement is realy slow. I am afraid that the db-server searches the whole pricelist for every item I search.</p>
  
  <p>I know the itemnr. But can I somehow search the correponding itemid and "send" this itemid to the subquery? So the subquery can find the highest price of this item quickly??? Or is there an other solution for my problem?</p>
  
  <p>Help please.</p>