Faster UPDATE and ALTER query in mySQL
€8-30 EUR
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.
Project ID: #7246530
About the project
Awarded to:
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.
2 freelancers are bidding on average €34 for this job
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
I have 6 years working with databases specialized in Olap which is a system that makes the databases go faster.