Faster UPDATE and ALTER query in mySQL

In Progress Posted Mar 4, 2015 Paid on delivery
In Progress Paid on delivery

Hi,

I need a faster solution to a few slow queries in mySQL. I need it very quickly (within the next couple of hours) so please only bid if you know how to solve it.

I have a simple procedure to run in mySQL. I have a table with a column called "temp" which is a varchar, I want to format this and place it in the column called "timestamp" which is (obviously) a timestamp. "temp" is not in the correct format in the .csv file hence I have to do some calculations in order to make it in timestamp format. The table has 200 million rows so speed is a big issue for me. Here is how I have done it so far:

Format in .csv file: 20090501 00:00:00.833

Query to format to timestamp:

update tbl set timestamp=(concat(left(temp,4), '-', substr(temp,5,2), '-', substr(temp,7,2), right(temp,13)));

If there is a way to load it directly into the table as I use "LOAD DATA LOCAL INFILE" that would work too.

The second update is the following which saves the hour of the timestamp into column 'thour' - this is extremely slow:

update tbl set thour=hour(timestamp);

The third performance blocker is this statement that only deletes a column - wondering why that should take so long time..

alter table tbl drop column temp;

If you bid for this project it should be because you know a way to improve the speed substantially on these standard SQL queries.

Database Programming MySQL SQL

Project ID: #7246530

About the project

2 proposals Remote project Active Mar 4, 2015

Awarded to:

vis6ky

hey, i can do this i am experience web developer in php and mysql. i can do this in a little bit of time. waiting for your response.

€34 EUR in 1 day
(1 Review)
0.6

2 freelancers are bidding on average €34 for this job

ricks47khan

Hello I am Emraan Khan with 5 year experience(15+ member Expert team with clear Vision) in Web-Application (PHP,HTML5,CSS,WordPress,Joomla & OpenCart etc) & Mobile Application with Gaming (Android,I-phone,PhoneGap,U More

€44 EUR in 1 day
(0 Reviews)
0.0
vasileiosch

I have 6 years working with databases specialized in Olap which is a system that makes the databases go faster.

€33 EUR in 1 day
(0 Reviews)
0.0