ActiveRecord原始sql比MySql慢

Question

我希望有人能帮助我理解为什么从ActiveRecord(33.6 s)执行原始sql查询的性能不如Mysql(14.0 s)。

查询如下：

SELECT 
      CASE 
        WHEN
        WeekDay(c.collect_date) >=0 AND WeekDay(c.collect_date) <5 THEN 1
      ELSE 0 
      END AS is_business_day, 
      HOUR(c.collect_date) consumption_hour, 
        SUM(c.energy), 
        AVG(c.power) 
    FROM consumptions c
    INNER JOIN devices d ON c.device_id=d.device_id AND d.ftp_id=1    
    GROUP BY CASE WHEN
        WeekDay(c.collect_date) >=0 AND WeekDay(c.collect_date) <5 THEN 1
      ELSE 0 
      END, HOUR(c.collect_date)

下面是“时代”的截图。

​

​

​

​

提前感谢！

Answer 1

考虑到从特征分析(见Rails Github问题)中获取的时间，得出的结论是：

ActiveRecord浪费的时间与Mysql相似，但比*Mysql工作台**慢。