Hi All, I am facing an issue with timestamp columns while working with MySQL load data in file, I am loading around a million records which is taking around 2 hours to complete the load data.
Before get into more details about the problem, first let me share the table structure. CREATE TABLE `test_load_data` ( `id1` int(11) DEFAULT NULL, `col10` varchar(255) DEFAULT NULL, `DB_CREATED_DATETIME` datetime DEFAULT NULL, `DB_MODIFIED_DATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; LOAD DATA LOCAL INFILE '/x.dat' INTO TABLE test_load_data FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' (id1,col10,@DB_CREATED_DATETIME) SET DB_CREATED_DATETIME = NOW() ; mysql> select DB_CREATED_DATETIME,DB_MODIFIED_DATETIME,count(1) from test_load_data group by DB_CREATED_DATETIME,DB_MODIFIED_DATETIME; +---------------------+----------------------+----------+ | DB_CREATED_DATETIME | DB_MODIFIED_DATETIME | count(1) | +---------------------+----------------------+----------+ | 2015-04-07 10:08:09 | 2015-04-07 10:08:09 | 1000000 | +---------------------+----------------------+----------+ 1 row in set (2.14 sec) The problem is , as mentioned the load data is taking around 2 hours, I have 2 timestamp columns for one column I am passing the input through load data, and for the column "DB_MODIFIED_DATETIME" no input is provided, At the end of the load data I could see only one timestamp value for both the columns, though the load data takes 2 hours to load the data. Can any one explain how exactly the load data infile works, and why only a single timestamp is inserting for all 1 million records though the load data taking around 2 hours. Thank you in advance. -- Thanks, Trimurthy P Mobile : +91 97397 64298 http://mysqlinternals.blogspot.in/ https://www.linkedin.com/pub/trimurthy-pothanaboyina/5a/9a9/96b
