MySQL优化

Question

我有一个名为“产品”的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

ps。这个查询成功了，但是非常慢，非常慢：

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

根据萨钦的回答和你的评论，也许这能帮上忙：

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，online)

• (public_id，

• ，id)

Answer 2

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

试试看这是否有效。子查询提供所有未被删除的public_id，然后为其运行一个在线筛选器。

Answer 3

这个很管用，但我不知道它的效率。基于Sachin的回答

select p.* from products p where p.public_id in 
(
   select p2.public_id from products p2
   group by p2.public_id
   having sum(p2.deleted) = 0
) 
and p.online = 1 
and p.id = (select max(p3.id) from products p3 where p3.public_id = p.public_id);