mysql - ActiveRecord raw 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

考虑到分析的时间(see Rails Github issue)，结论是:

The wasted time by ActiveRecord is similar to Mysql, but slower than *Mysql WorkBench**