MySQL - 找出要使用的索引，并且不能按预期工作 [英] MySQL - Figuring out what indexes to use, AND not working as expected

问题描述

我在这里遇到两个问题，我正试图解决。首先，查询，用值替换？因为我正在使用准备好的声明：

I have two problems here I'm trying to solve. First of all, the query, with values replaced with ? since I'm using a prepared statement:

SELECT  *
    FROM  Messages t
    WHERE  perspective_user=?
      and  timestamp BETWEEN ? AND ?
      AND  timestamp_added = 
        ( SELECT  max(timestamp_added)
            from  Messages t2
            where  t2.author = t.author
              AND  t2.body = t.body
              AND  t2.timestamp = t.timestamp
              AND  t2.timestamp_added <= ?  )
      AND  convo_id IN (
        SELECT  convo_id
            FROM  Conversations
            WHERE  perspective_user=?
              AND  identity=?
              AND  timestamp_added=? );

代码非常慢，但没有以下行。包含它，它返回0结果：

The code is incredibly slow but works without the following line. With it included, it returns 0 results:

      AND  convo_id IN (
        SELECT  convo_id
            FROM  Conversations
            WHERE  perspective_user=?
              AND  identity=?
              AND  timestamp_added=? );

该行自行运行良好，并且有些行满足上述要求以及线。我想知道为什么它表现得如此奇怪。

That line works fine on its own, and there are rows that satisfy the above requirements as well as that line. I'm wondering why it's behaving so odd.

我想知道的另一件事是要添加的索引。我的第一个想法是三个索引：一个在perspective_user，timestamp，timestamp_added和convo_id上，另一个是关于author，body，timestamp和timestamp_added的索引，以及关于perspective_user，identity和timestamp_added的最终索引。

The other thing I'm wondering about is what indexes to add. My first thought was three indexes: One on perspective_user, timestamp, timestamp_added, and convo_id, a second index on author, body, timestamp, and timestamp_added, and a final index on perspective_user, identity, and timestamp_added.

我真的不懂MySQL，所以我不知道如何继续。

I don't really understand MySQL so I'm not sure how to proceed.

推荐答案

IN（SELECT ...）是一个非常低效的MySQL构造。而是 JOIN对话ON convo_id = ... WHERE ... 。

IN ( SELECT ... ) is a very inefficient construct in MySQL. Instead JOIN Conversations ON convo_id = ... WHERE ....

已添加游戏似乎是groupwise max。请参阅 this 进行讨论有效的方法。

The added game appears to be a "groupwise max". See this for discussion of efficient ways to do that.

请提供 SHOW CREATE TABLE 。如果 body 不是 TEXT ，那么你需要 INDEX（作者，正文，时间戳，timestamp_added ）。如果这不起作用，那么我们需要更好地理解消息的结构。

Please provide SHOW CREATE TABLE. If body is not TEXT, then you need INDEX(author, body, timestamp, timestamp_added). If that won't work, then we need to better understand the structure of Messages.

这篇关于MySQL - 找出要使用的索引，并且不能按预期工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！