您如何优化连接自身并执行“自定义"操作的 MySQL 查询?通过...分组? [英] How do you optimize a MySQL query that joins on itself and does a "custom" group by?

问题描述

我有以下查询随着数据库表大小的增加而开始变慢:

I have the following query that is starting to become slow as the size of the DB table increases:

SELECT  
    t.*, 
    e.TranslatedValue AS EnglishValue
FROM (
    SELECT DISTINCT PropertyKey 
    FROM Translations
) grouper
JOIN Translations t 
    ON t.TranslationId = (
        SELECT TranslationId 
        FROM Translations gt
        WHERE gt.PropertyKey = grouper.PropertyKey 
            AND gt.Locale = 'es' 
            AND gt.Priority = 3
        ORDER BY gt.ModifiedDate DESC 
        LIMIT 1
    )
INNER JOIN Translations e 
    ON t.EnglishTranslationId = e.TranslationId 
ORDER BY t.ReviewerValidated, PropertyKey

首先，我从 Translations 中选择所有内容，并与其自身结合以获得相应的英语值.

First, I am selecting everything from Translations, joined with itself to get me the corresponding English value also.

然后，我想将结果限制为每个 PropertyKey 只有一个.这就像一个 group by ，除了我需要选择一个特定的记录作为返回的记录(而不是 group by 的方式只给我它找到的第一个记录).这就是为什么我有一个只返回一个 TranslationId 的内部查询.

Then, I then want to limit my results to only one per PropertyKey. This is like a group by except I need to pick a specific record to be the one returned (instead of the way group by just gives me the first one it finds). That is why I have the inner query that just returns one TranslationId.

当我运行解释时，我得到以下信息:

When I run explain I get the following info:

有没有一种方法可以返回相同的结果集而不必让 MySQL 使用较慢的派生表?谢谢！

Is there a way I can return the same set of results without having to have MySQL use a slower derived table? Thanks!

更新:我用架构和示例数据创建了一个 SQL Fiddle.你可以自己运行我的查询以查看它给出的结果.我需要成为能够获得相同的结果，希望以更快的方式.http://sqlfiddle.com/#!2/44eb0/3/0

UPDATE: I created an SQL Fiddle with a schema and sample data. You can run my query for yourself to see the results it gives. I need to be able to get the same results, hopefully in a faster way. http://sqlfiddle.com/#!2/44eb0/3/0

推荐答案

我认为您需要与 PropertyKey 匹配的给定本地和优先级的最新 TranslatedValue记录.

I think you want the most recent TranslatedValue for the given local and priority that matches PropertyKey in a record.

如果是这样，以下使用单个相关子查询执行您想要的操作:

If so, the following does what you want, using a single correlated subquery:

 select t.*,
        (select t2.TranslatedValue
         from Translations t2
         where t.PropertyKey = t2.PropertyKey and
               t2.Locale = 'es' and
               t2.Priority = 3
         order by t.ModifiedDate desc
         limit 1
        ) as EnglishValue
 from Translations t
 having EnglishValue is not NULL
 ORDER BY t.ReviewerValidated, PropertyKey;

(have 子句消除了没有翻译的记录.)

(The having clause eliminates records with no translation.)

如果是这样，那么 Translations(PropertyKey, Locale, Priority, ModifiedDate) 上的索引应该会加快查询速度.

If so, then an index on Translations(PropertyKey, Locale, Priority, ModifiedDate) should speed up the query.

这篇关于您如何优化连接自身并执行“自定义"操作的 MySQL 查询?通过...分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！