title: MySQL Optimisation
body: <p>I have mysql-table called products with the following structure/contents:</p>
  
  <pre><code>id (int), public_id (int), deleted (bool), online (bool)
  1         1                0               1
  2         1                0               0
  3         1                1               0
  4         2                0               1
  5         2                0               1
  </code></pre>
  
  <p>My question is, how to select all, currently online, not deleted products. In this example only record 5 (public_id 2). The same public_id means the same product (grouping) The higher the id, the newer the information (ordering). And the products need to be <em>not</em> deleted (where). And some other where statements, in this case with the online field.</p>
  
  <p>I need all the aspects (grouping, ordering and where), but I can't figure out how.</p>
  
  <p>Any suggestions?</p>
  
  <p>Results of explain query from Galz:</p>
  
  <pre><code>id  select_type         table         type   possible_keys  key       key_len  ref    rows  Extra
  1   PRIMARY             nomis_houses  ref    online         online    1        const  8086  Using where
  2   DEPENDENT SUBQUERY  nomis_houses  index  NULL           house_id  4        NULL   9570  Using filesort
  </code></pre>
  
  <p>ps. This query did the trick, but is awfully, awfully, awfully slow:</p>
  
  <pre><code>select * from 
   (select * from 
    (select * from products order by id desc) tmp_products_1 
   group by public_id) tmp_products_2 
  where deleted = '0' AND online = '1'
  </code></pre>