MySQL优化

Question

我有一个名为 products 的 mysql 表，其结构/内容如下:

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

我的问题是，如何选择所有当前在线且未删除的产品。在此示例中仅记录 5 (public_id 2)。相同的public_id表示相同的产品(分组) id越高，信息越新(排序)。并且产品需要不删除(哪里)。以及其他一些 where 语句，在本例中为在线字段。

我需要所有方面(分组、排序和位置)，但我不知道怎么做。

有什么建议吗？

来自 Galz 的解释查询结果:

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

附言。这个查询成功了，但是非常非常慢:

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'

Answer 1

根据 Sachin 的回答和您的评论，这可能会有所帮助:

select * from products where id in 
(
   select max(id) as id from products
   where sum(deleted) = 0
   group by public_id
) 
and online = 1

Pentium10编辑

查询可以改写成

SELECT * 
FROM   products p 
       JOIN (SELECT MAX(id) AS id 
             FROM   products 
             HAVING  SUM(deleted) = 0 
             GROUP  BY public_id) d 
         ON d.id = p.id 
WHERE  online = 1 

您需要索引:

• (id,在线)
• (public_id,deleted,id)