indexing - MySQL Index calculation? -

i have query optimization if possible it's taking 15 seconds run.

it querying large db approx 1000000 records , slowed down grouping hour (which derived date_format()).

i indexed relevant fields in tables improved performance don't know how or if it's possible create index hour group since it's not field...

i realise dataset large i'd know if have options.

any appreciated! thanks!

select  `id`,     tbl1.num,     name,     date_format(`timestamp`,'%x-%v') wknum,     date_format(`timestamp`,'%y-%m-%d') date,     date_format(`timestamp`,'%h') hour,     if(code<>0,codedescription,'') status,     sum(time_to_sec(`timeblock`))/60 time,      sum(`distance`) distance,      sum(`distance`)/(sum(time_to_sec(`timeblock`))/60) speed  `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 `timestamp`>'2013-07-28 00:00:00' group `num`,date_format(`timestamp`,'%h'),`mcstatus` 

mysql can’t use indexes on columns unless columns isolated in query. isolating column means should not part of expression or inside function in query.

solutions:

1-you can store hour separate timestamp column. example can store both before insert , before update triggers.

delimiter $$ create trigger `before_update_hour` before update on `tbl1` each row begin      if new.`timestamp` != old.`timestamp`        set new.`hour` = date_format( new.`timestamp`,'%h')      end if; end; $$ delimiter ;   delimiter $$  create trigger `before_insert_hour`  before insert on `tbl1`  each row  begin      set new.`hour` = date_format( new.`timestamp`,'%h')  end;  $$  delimiter ; 

2-if can use mariadb, can use mariadb virtual columns.