Mysql – Get the Time of Last update of a column

information-schemaMySQLrow-modification-time

This command gives the date of the last update for a table

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'MyDB'
AND TABLE_NAME = 'MyTable'

But I want to find the time of last update of a particular column of a table.
I can't use triggers because I want to know the time of last update of a specific column from a system table.

I hope I explained well my problem.

Best Answer

None of the system tables (that is, nothing in the INFORMATION_SCHEMA database) exist that has that kind of information recorded anywhere. In other words, there is no native mechanism to put any timestamps on column changes. Any time that

one or more columns change in any row
a new row is added
an old is deleted
an ALTER TABLE of any kind

the UPDATE_TIME column in INFORMATION_SCHEMA.TABLES is updated.

SUGGESTION

This may be a bitter pill to swallow, but you must do the following:

Create a custom table that records
- primary key
- column name
- the old value of that column
- the new value of that column
- timestamp of the change
Create BEFORE UPDATE trigger to compare old and new columns and record it if they do not match

EPILOGUE

This may not be so bad if you are customizing for a few tables and a few columns.

Related Solutions

Thesql trigger to insert/update date column from datetime column

It's unclear why you need this but assuming that the table looks something like

CREATE TABLE Table1
    (`id` int not null auto_increment primary key, 
     `dt1` datetime, 
     `dt2` datetime, 
     `d1`  date, 
     `d2`  date);

your triggers might look the following way

CREATE TRIGGER tg_bi_table1
BEFORE INSERT ON table1
FOR EACH ROW
  SET NEW.d1 = DATE(NEW.dt1), 
      NEW.d2 = DATE(NEW.dt2);

CREATE TRIGGER tg_bu_table1
BEFORE UPDATE ON table1
FOR EACH ROW
  SET NEW.d1 = DATE(NEW.dt1), 
      NEW.d2 = DATE(NEW.dt2);

Here is SQLFiddle demo

Mysql – Adding index to large thesql tables

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git