MySQL索引计算？

Question

我有一个查询，如果可能的话可以进行优化，因为它需要 15 秒才能运行。

它正在查询一个包含大约 1000000 条记录的大型数据库，并且通过按小时分组(从 DATE_FORMAT() 派生)减慢了速度。

我为所有表中的所有相关字段编制了索引，这显着提高了性能，但我不知道如何或者是否有可能为小时组创建索引，因为它不是一个字段...

我确实意识到数据集非常大，但我想知道我是否有任何选择。

任何帮助将不胜感激!谢谢!

SELECT  `id`,
    tbl1.num,
    name,
    DATE_FORMAT(`timestamp`,'%x-%v') AS wknum,
    DATE_FORMAT(`timestamp`,'%Y-%m-%d') AS date,
    DATE_FORMAT(`timestamp`,'%H') as hour,
    IF(code<>0,codedescription,'') AS status,
    SUM(TIME_TO_SEC(`timeblock`))/60 AS time, 
    SUM(`distance`) AS distance, 
    SUM(`distance`)/(SUM(TIME_TO_SEC(`timeblock`))/60) AS speed
FROM  `tbl1`
LEFT JOIN `tbl2` ON tbl1.code = tbl2.code 
LEFT JOIN `tbl3` ON tbl1.status = tbl3.status 
LEFT JOIN `tbl4` ON tbl1.conditionnum = tbl4.conditionnum 
LEFT JOIN `tbl5` ON tbl1.num = edm_mc_list.num
WHERE `timestamp`>'2013-07-28 00:00:00'
GROUP BY `num`,DATE_FORMAT(`timestamp`,'%H'),`mcstatus`

Answer 1

MySQL 通常不能在列上使用索引，除非列是在查询中隔离。 隔离列意味着它不应是表达式的一部分或位于查询的函数内。

解决方案:

1-您可以将小时与 timestamp 列分开存储。例如，您可以通过 before insert 和 before update 触发器来存储它。

DELIMITER $$
CREATE TRIGGER `before_update_hour`
BEFORE UPDATE ON `tbl1`
FOR EACH ROW
BEGIN
     IF NEW.`timestamp` != OLD.`timestamp` THEN
       SET NEW.`hour` = DATE_FORMAT( NEW.`timestamp`,'%H')
     END IF;
END;
$$
DELIMITER ;

 DELIMITER $$
 CREATE TRIGGER `before_insert_hour`
 BEFORE INSERT ON `tbl1`
 FOR EACH ROW
 BEGIN
     SET NEW.`hour` = DATE_FORMAT( NEW.`timestamp`,'%H')
 END;
 $$
 DELIMITER ;

2-能用MariaDB就用MariaDB virtual columns .