CODEWITHSUNDEEP
mysqlindexingquery-optimization
doovers
doovers

Reputation: 8685

MySQL Index calculation?

I have a Query that could do with optimization if possible as it's taking 15 seconds to run.

It is querying a large db with approx 1000000 records and is slowed down by grouping by hour (which is derived from DATE_FORMAT()).

I indexed all relevant fields in all the tables which improved the performance significantly but I don't know how to or if it's even possible to create an index for the hour group since it's not a field...

I do realise the dataset is very large but I'd like to know if I have any options.

Any help would be appreciated! Thanks!

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`

Upvotes: 2

Views: 2227

Answers (1)

Arash Mousavi
Arash Mousavi

Reputation: 2148

MySQL generally can’t use indexes on columns unless the columns are isolated in the query. Isolating the column means it should not be part of an expression or be inside a function in the query.

Solutions:

1-You can store hour separate from timestamp column. for example you can store it by both before insert and before update triggers.

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-If you can use MariaDB, you can use MariaDB virtual columns.

Upvotes: 1

Related Questions