如何加快我的MySQL查询速度，获取每个用户最近一次交易的AVG？

I'm pretty sure my implementation for this solution is the least efficient (takes 2 seconds to complete when run over only 30,000 records). Is there a faster way?

我非常确定我对此解决方案的实现效率最低（仅运行30,000条记录需要2秒才能完成）。有更快的方法吗？

My MySQL query, followed by explanation below:

我的MySQL查询，后面的解释如下：

SELECT  DATE(m.date) AS day,
        AVG(t.amount) AS amount
FROM    transactions s

            LEFT JOIN users m
                ON m.id = s.user_id
WHERE   
        #only consider the most recent transaction for each user
        s.id = (
        SELECT  id
        FROM    transactions s2
        WHERE   s2.user_id = s.user_id
                AND s2.created_date = (
                    SELECT  MAX(created_date)
                    FROM    transactions s3
                    WHERE   s3.user_id = s.user_id
                )
        )
GROUP BY day
ORDER BY day;

Basically it's saying "show the average transaction amount per day, considering only each user's most recent transaction".

基本上它是说“只显示每个用户最近的交易，显示每天的平均交易金额”。

I've already created an index on created_date.

我已经在created_date上创建了一个索引。

I don't want to just select the MAX(transaction.id) for user related transactions, because there's no guarantee that new transaction records added to the table are always for newer real world transactions.

我不想仅为用户相关事务选择MAX（transaction.id），因为无法保证添加到表中的新事务记录始终用于较新的真实世界事务。

1 个解决方案

#1

Three selects looks like too many.

三个选择看起来太多了。

SELECT t.date, avg(t.amount) 
FROM transactions t
JOIN 
  (SELECT user_id, max(created_date) AS max_date 
   FROM transactions GROUP BY user_id) AS t2
ON t.user_id=t2.user_id and t.created_date=max_date
GROUP BY t.date
ORDER BY t.date;

Note this includes all transactions by a given user on the most recent day he had any transactions. There doesn't seem to be a timestamp.

请注意，这包括给定用户在最近一天进行任何交易时的所有交易。似乎没有时间戳。

Make sure there is a composite index on user_id, created_date.

确保user_id，created_date上有一个复合索引。

#1