Mysql - create an indexed hour column from an existing timestamp

Question

I have a column called "updatetime" which is a timestamp ("2011-02-01 09:00:51"). To achieve performance, I need to create an indexed column "updatetime_hour" depending on the timestamp hour.

So, for example, if "updatetime" was "2011-02-01 09:00:51", then "updatetime_hour" would be "9".

I am trying to do all this in mysql, although PHP is also an option. 60k + existing lines.

Thoughts?

Answer 1

UPDATE yourtable SET updatetime_hour=HOUR(updatetime); 

Do not run this during rush hours, it will take some time. You can even run it in smaller batches - make updatetime_hour a null value and continue doing this until you get the "0 lines affected":

 UPDATE yourtable SET updatetime_hour=HOUR(updatetime) WHERE updatetime_hour IS NULL LIMIT 1000; 

Answer 2

To do this automatically every time you add or update a row, use triggers:

 CREATE TRIGGER t1 BEFORE INSERT ON table FOR EACH ROW BEGIN SET NEW.updatetime_hour = HOUR(NEW.updatetime); END CREATE TRIGGER t2 BEFORE UPDATE ON table FOR EACH ROW BEGIN SET NEW.updatetime_hour = HOUR(NEW.updatetime); END