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