MySQL Forums
Forum List  »  Optimizer & Parser

Re: Normalize dump table
Posted by: Antonio Valle
Date: October 13, 2015 01:41PM

This was my solution, the complete description it here :
http://stackoverflow.com/questions/11430362/update-column-from-another-table-in-large-mysql-db-7-million-rows

<?php
date_default_timezone_set('America/Mazatlan');
$started = time();
$i = 0;
$total = 7904626;

echo "Started at " . date('H:i:s', $started) . PHP_EOL;

function timef($s){
$h = round($s / 3600);
$h = str_pad($h, 2, '0', STR_PAD_LEFT);
$s = $s % 3600;
$m = round( $s / 60);
$m = str_pad($m, 2, '0', STR_PAD_LEFT);
$s = $s % 60;
$s = str_pad($s, 2, '0', STR_PAD_LEFT);
return "$h:$m:$s";
}

$cnn = mysql_connect("127.0.0.1","root","");
mysql_select_db("consultas",$cnn);

while (1){

$j = $i * 5000;
$k = $j + 4999;
$result = mysql_query("
UPDATE venta_dump d
join vehiculo p on d.identificador = p.identificador
set d.vehiculo_id = p.id_vehiculo
where d.id between $j and $k
");
$i++;
if(!$result) die(mysql_error());
if(mysql_affected_rows() == 0) die(PHP_EOL . 'Done!');
$p = round(($i * 5000) / $total, 4) * 100;
$s = time() - $started;
$ela = timef($s);
$eta = timef( (( $s / $p ) * 100) - $s );
$eq = floor($p/10);
$show_gt = ($p == 100);
$spaces = $show_gt ? 9 - $eq : 10 - $eq;
echo "\r {$p}% | [" . str_repeat('=', $eq) . ( $show_gt ? '' : '>' ) . str_repeat(' ', $spaces) . "] | Elapsed: ${ela} | ETA: ${eta}";
}

Options: ReplyQuote


Subject
Views
Written By
Posted
2401
October 11, 2015 08:37PM
854
October 11, 2015 08:42PM
Re: Normalize dump table
1059
October 13, 2015 01:41PM
958
October 17, 2015 07:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.