optimize mysql taking too long

Question

Today I made a clumsy update without WHERE. Yeah, I know. I have backups but I found a way to fix it without loosing anything. It implies a SELECT and a loop of 50.000 updates, not a fan of this approach.

Maybe I can merge the SELECT and the UPDATE so it's only a big query, witch in my head seems like a better approach.

This is table trabajo. One trabajo per row.

CREATE TABLE `trabajo` (
`id` INT(30) NOT NULL AUTO_INCREMENT,
`numeroEntrada` VARCHAR(30) NOT NULL COLLATE 'utf8_unicode_ci',
`personaContacto` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`detallesTrabajo` VARCHAR(255) NOT NULL COLLATE 'utf8_spanish_ci',
`estadoActual` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_spanish_ci',
`importeTrabajo` FLOAT NULL DEFAULT NULL,
........
PRIMARY KEY (`id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=51259;

This is table tareastrabajo. Each trabajo has 5 rows on tareastrabajo.

CREATE TABLE `tareastrabajo` (
`numeroEntrada` VARCHAR(30) NOT NULL COLLATE 'utf8_unicode_ci',
`nombreTarea` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
`fechaCompletada` DATETIME NULL DEFAULT NULL,
`usuario` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`descripcion` VARCHAR(255) NOT NULL COMMENT 'Descripciones sobre cada fase' COLLATE 'utf8_unicode_ci',
`seccion` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
PRIMARY KEY (`numeroEntrada`, `nombreTarea`)
)
COMMENT='Indica las tareas que posee un trabajo'
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;

In trabajo there's a field numeroEntrada that links into numeroEntrada in tareastrabajo. tareastrabajo saves the different status of each trabajo and when it changed status. To make everything easier, estadoActual in trabajo saves the current status.

The thing is I changed all the estadoActual to the same value. By checking the last nombreTarea modified (checking the newest fechacompletada) I can get the current status.

This is the php code:

<?php include "inc/config.php"; ?>
<?php include "inc/funciones.php"; ?>
<?php header('Content-Type: text/html; charset=UTF-8'); ?>
<?php 
if(mysql_select_db($db_db2)){
 echo "connected<br>";
}
$query = 'SELECT * FROM trabajo where estadoActual="Entrega"';
mysql_query("SET NAMES 'utf8'");
$result = mysql_query($query);
$i = 0;
mysql_query("SET NAMES 'utf8'");
$resultado = mysql_query($query);
try {
 while($estado = mysql_fetch_assoc($resultado)){
 $query = 'UPDATE trabajo set estadoActual=(SELECT nombreTarea FROM tareastrabajo where numeroEntrada = "'.$estado["numeroEntrada"].'" AND fechaCompletada is not null order by fechaCompletada DESC limit 1) where numeroEntrada="'.$estado["numeroEntrada"].'"';
 mysql_query("SET NAMES 'utf8'");
 $result = mysql_query($query);
 $i++;
 }
} catch (Exception $e) {
 echo $e->getMessage();
 echo "---";
 echo mysql_error();
}
echo "<br>".$i." rows";
?>

witch takes 0.8 seconds per row.

DISCLAIMER: Please avoid answers and comments pointing out about mysqli or PDO. I understand the issues myself and we're in the process of migrating it. Meanwhile, we have to deal with this.

• Jamal: Please specify only the code's purpose in the title. Specific requests are only needed in the post body.

Answer 1

I'm blindfolded by spanish names a bit. I understood that you're trying to...

set tareastrabajo.nombreTarea with newest and specified DATETIME of tareastrabajo.fechaCompletada into trabajo.estadoActual if it's value is 'Entrega'

If I got it right, then this single query should work:

UPDATE trabajo t
INNER JOIN tareastrabajo nt ON nt.numeroEntrada = t.numeroEntrada
INNER JOIN (
 SELECT numeroEntrada, MAX(fechaCompletada) AS max_fechaCompletada
 FROM tareastrabajo
 GROUP BY numeroEntrada
) tmp ON tmp.numeroEntrada = nt.numeroEntrada AND tmp.max_fechaCompletada = nt.fechaCompletada
SET t.estadoActual = nt.nombreTarea
WHERE t.estadoActual = 'Entrega'
AND nt.fechaCompletada IS NOT NULL

Use capital letters for query keywords and consider english names next time - (foreigners' struggle aside) it's always awkward to read them mixed with english based syntax.

• monxas: I'll test that query, thanks! about spanish names, you're right. It's inherited code, but I should have translated it in my question. My apologies