mysql power import, handling dates

paolodit

I have generally used php for manipulating variables, this worked fine up to 10,000 records, however now I have to deal with batches of 1 million entry imports, am doing some serious data mining!! I need to use Mysql a bit more cleverly! be a hardcore mysql power user, so help!!

What I was doing, was doing a:

LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE dump_data FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\r' " ;

This part is quite fast!

Then when I had in the data in table do a "SELECT * from dump_data" and using php mysql_fetch_row go thru it entry-by-entry. Then preparing the date from a VACHAR field to a mysql DATETIME ready variable, then inserting it into the another table.

So I'd run a php function that did this with each start date the start date:

// start_date format: 04/12/2005  18:36:00
// mysql desired date format: 2004-12-25  18:36:00

$len = strlen($the_date);
$year = substr($the_date, 6, 4);
$month = substr($the_date, 3, 2);
$date = substr($the_date, 0, 2);
$time = substr($the_date, 11, 5) . ":00";

$new_date = "$year-$month-$date $time";

However with 1 million entries, this takes php & mysql 7 minutes, to list each entry, and insert individually.

I have increased my timeout to 30 mins, and give 250mb to PHP in the php.ini file. But this is not the answer me thinks!?

There must be a way of either:

a) using mysql load data, to automatically structure the date, ready for mysql

b) copy the data from one table to another, with restructuring the date data using a mysql function

c) load the data as it is, and alter table from a varchar to datetime DEFAULT '0000-00-00 00:00', handling the data differences

Any ideas mysql geniuses !? Thanks!

liquorvicar

Have you tried using INSERT INTO new_table (fields...) SELECT * FROM old_table combined with MySQL's string functions?

Did you really mean:

// start_date format: 04/12/2005 18:36:00
// mysql desired date format: 2004-12-25 18:36:00

Or did you mean

// start_date format: 04/12/25 18:36:00
// mysql desired date format: 2004-12-25 18:36:00

If it's the latter, them MySQL will parse dates of format 04/12/25 18:36:00 into 2004-12-25 18:36:00 automatically.

paolodit

Ah! Sorry! I meant:

// start_date format: 04/12/25 18:36
// mysql desired date format: 2004-12-25 18:36:00

mysql string functions sound interesting? what are they?

Roger_Ramjet

What you need to do to convert from dd/mm/yyyy to ISO date(mysql use the ISO8601 date format) is str_to_date() which converts from the input according to the format string supplied. Needs version 4.1 by the way.

Liquorvicar, mysql will not automatically parse dates for you which is why you need the str_to_date() function.

Paolodit, load the raw data into a work table first, get it into the database as fast as you can. Then select from there into your main table converting the dates in the query.

liquorvicar

MySQL does try and interpret date formats - the example given works, MySQL succesfully parses it and stores it in it's own format - but it's a good point Roger, it wouldn't work for all dates so you would need to do the conversion yourself.

paolodit

THANKYOU TO EVERYONE! ROGER & LICOR VICOR!

With your help I have speeded up several processes, taking PHP out of the equation.

1) I first do a quick load of 1m records:
LOAD DATA LOCAL INFILE '$filename' INTO TABLE dump_prices

nice.... ultra fast...

2) Then I update all the date fields, to Mysql friendly, using some of the mysql string maniuplation functions:

UPDATE dump_prices SET
settled_date=CONCAT(SUBSTRING(settled_date FROM 7 FOR 4), '-', SUBSTRING(settled_date FROM 4 FOR 2), '-', SUBSTRING(settled_date FROM 1 FOR 2), ' ', SUBSTRING(settled_date FROM 12 FOR 5), ':00'),

etc... not too neat, but fairly fast...

3) Then I do an INSERT INTO ... SELECT... Which I never knew you could do, into my properly structured table. This is so much better than individual inserts!

The whole import routine for 1m records, is down to 3 mins. Compared to about 60 mins for PHP.

I still have some more routines to optimise, but thanks, for your POWER MYSQL USER TIPS!

love & Pies, Paolo

Roger_Ramjet

Glad we could help.

Sorry, Liquorvicar, you were right. I did not see that these were yy/mm/dd which will work as you say. I thought it was US date mm/dd/yy which mysql cannot handle.

Personally, I'd cut it down to 2 operations, doing the conversion in the INSERT...SELECT itself like this

"INSERT INTO table SELECT col1, STR_TO_DATE(settled_date, '%y/%m/%d %h:%m'), col3 FROM dump_prices"

What that is doing is to tell mysql what the existing date field format is, '%y/%m/%d %h:%m', so that it can convert into it's native date format. Should be quicker still.

paolodit

I have tried this str_to_date thing... Hmmm But can't seem to get it to work. So I have to stick with Concat, which is probably slower?

Is it because I am using Mysql 3.23 ?? And I need a higher version?

I am using PHPtriad, which is an Apache, php&mysql setup for Windows. And probably a bit dated.. If I need Mysql 4.x or higher.. Does anyone know an easy to install setup of this?

hmmm

Roger_Ramjet

Yes, it needs 4.1, but then almost everything usefull needs that so you must upgrade. Try the INSTALL forum for advise on that.

paolodit

Ok. I will look into upgrading at some point, with a search of the install forum. Thanks... i can see that MYSQL 4+ is a bundle of joy..

My next problem in need of mysql power user techniques, is a tricky one! Am not sure its so easily solvable! So here it is!:

I get all the events, with number of people not yet set:

$sql1 = "SELECT event_id FROM events WHERE number_people='null' " ;

Then for each event_id, I want to look up how many people there are:

$sql2 = "SELECT count(distinct(person_id)) FROM prices WHERE event_id ='$event_id' ";

And update the original number of people:

$sql3 = "UPDATE events SET number_people='$number_people' WHERE event_id='$event_id' ";

Again, using PHP as the intermediateary, this takes about 20 mins to process a million records. It was fine for 10,000. but now argh! Help! Any ideas Mysql experts?!

My desired mysql expression would do something like, this:

$sql4 = "
UPDATE events

SET number_people=count(distinct(person_id))
FROM prices WHERE events.event_id=prices.event_id ";

But, that obviously, doesnt work! There must be an easy answer? Thanks!

paolodit

Ok, I have managed to cut it down to 2 rather than 3 operations, I am now using GROUP BY..

$sql1 = "SELECT count(distinct(person_id)), event_id FROM prices GROUP BY event_id";

But I am still doing a PHP loop to update for each individual event_id:

$sql2 = "UPDATE events SET number_people='$number_people' WHERE event_id='$event_id'";

How do I merge an update & a select at same time in one operation? hmmm

In japan the word for "problem" is the same as the word for "opportunity". However, the word for "golf" is also the same as "flying tree-monkey".

Roger_Ramjet

With the SELECT as a subquery - if you have version 4.1

"UPDATE events e INNER JOIN (SELECT event_id, COUNT(DISTINCT(person_id)) AS cnt FROM prices GROUP BY event_id) AS c USING(event_id)
SET e.number_people=c.cnt WHERE e.event_id=c.event_id"

There are other wasy to write this with a subquery as well.

paolodit

Thanks Roger.

i have updated to php5 & mysql 5 on my dev server.

I like that inner join, that is very cool, and saves a lot of php SQL execution time.. many THANKS! I shall now try a few more without my training wheels on. 😉

Mochos Gracias dude

paolodit

I got on pretty well with optimising 2-3 queries into 1, with joins.. but now I seem to be stuck, again! I need some more POWER MYSQL techniques....So Help, this time with if clauses & variables inside mysql!..

First I get all the selections, in price order (cheapest first):

$sql1 = "SELECT event_id, selection_id FROM selections ORDER BY event_id, start_price Asc";

Then for each event, I do a mini-leaderboard of the prices for each selection. So the counter resets to 1 for each event, and count up, till end of selections for that event.

if ($this_event_id == $event_id) { $fave_position++; }
else { $fave_position = 1; $this_event_id = $event_id; }

Then obviously I update each of the selection entries:

$sql2 = "UPDATE selections SET fave_order='$fave_position' WHERE event_id='$event_id' AND selection_id='$selection_id ";

Again with php and 1m records, this takes 26 mins to update. I need to use some of mysql's variable functionality. like @my_variable. & SET @my_variable=1 and if clauses. I have beeen trying for 3 hours, help!

I'lll buy you a paypal beer roger!

Thanks, Paolo

top tip: hard feet? Your trusty cheese grater can overcome, any of your hard foot related issues, simply grate & smile!

paolodit

Nuts, it is a tricky one. I dreamt that I had solved this morning. Butt, my theory was incorrect 🙁

Any ideas??

paolodit

I have worked out how to do mysql IF clauses with user-settable mysql variables.

SET @n :=0 ;

SELECT event_id, selection_id, start_price,
IF (@this_event_id := event_id, @n := @n+1, @n := 1 ) AS the_position
FROM selections
ORDER BY event_id, start_price Asc

But I have a few more things to work out, such as how to seperate multiple of @variables inside if clauses...

Then to work out the UPDATE.