title: Optimize Hibernate HQL SELECT with IN clause
body: <p>Please how can I optimize this query?</p>
  
  <pre><code>FROM OrderEntity WHERE id IN (SELECT DISTINCT orderId FROM ProductEntity 
    WHERE code = :myCode OR extCode = :myCode)
  </code></pre>
  
  <p>This query is too slow for the tables which has around million rows</p>
  
  <p>Order 1 ... * Product</p>
  
  <p>Thank you for help</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>In pure sql I would use this query which is fast, but I don't know how to form it in HQL:</p>
  
  <pre><code>SELECT * FROM order o, (SELECT DISTINCT order_id FROM product WHERE code = 'ABC' 
     OR ext_code = 'ABC') p WHERE o.id = p.order_id
  </code></pre>