leoarce leoarce - 5 months ago 12
MySQL Question

What is fastest way to update multiple columns in a table from data of another table where updating table column(s) are something?

I have these two queries which work, but they are slow as can be. What is faster, or rather fastest way of doing this?

method 1)

$query = "
UPDATE list_data_extra
INNER JOIN list_data
ON (list_data_extra.serial_no = list_data.serial_no)
SET
list_data_extra.id = list_data.id,
list_data_extra.cid = list_data.cid,
list_data_extra.first = list_data.first,
list_data_extra.last = list_data.last,
list_data_extra.tracking_number = list_data.tracking_number
WHERE list_data_extra.id='0' AND list_data_extra.cid='0'
";


method 2)

$query = "UPDATE list_data_extra INNER JOIN list_data USING (serial_no) SET list_data_extra.id = list_data.id, list_data_extra.cid = list_data.cid, list_data_extra.first = list_data.first, list_data_extra.last = list_data.last, list_data_extra.tracking_number = list_data.tracking_number WHERE list_data_extra.id='0' AND list_data_extra.cid='0'";


Not sure this other method would be faster:

method 3)

$query="SELECT * FROM list_data_extra WHERE id='0' AND cid='0'";
$result=mysql_query($query);
$num_rows = mysql_num_rows($result);
if ($num_rows > 0) {
while($row=mysql_fetch_array($result)) {
$querytwo = mysql_fetch_array(mysql_query("SELECT id, cid, first, last, tracking_number FROM list_data WHERE serial_no='".$row['serial_no']."'"), MYSQL_ASSOC);
$querythree = "UPDATE list_data_extra SET id='".$querytwo["id"]."', cid='".$querytwo["cid"]."', first='".$querytwo["first"]."', last='".$querytwo["last"]."', tracking_number='".$querytwo["tracking_number"]."'";
mysql_query($querythree);
}
}


Another thing i tried is this, which is building entire query then executing it all at once, which is a bit faster than above, but still slow as junk. the above is like 9 minutes per 1000 records and this here below is like 5 minutes per 1000.

method 4)

$query="SELECT * FROM list_data_extra WHERE id='0' AND cid='0'";
$result=mysql_query($query);
$num_rows = mysql_num_rows($result);
if ($num_rows > 0) {
$id_loop = "";
$cid_loop = "";
$first_loop = "";
$last_loop = "";
$trackingnumber_loop = "";
$listids = "";
while($row=mysql_fetch_array($result)) {
$querytwo = mysql_fetch_array(mysql_query("SELECT id, cid, first, last, tracking_number FROM list_data WHERE serial_no='".$row['serial_no']."'"), MYSQL_ASSOC);
$id_loop .= "WHEN ".$row['listid']." THEN '".$querytwo["id"]."' ";
$cid_loop .= "WHEN ".$row['listid']." THEN '".$querytwo["cid"]."' ";
$first_loop .= "WHEN ".$row['listid']." THEN '".$querytwo["first"]."' ";
$last_loop .= "WHEN ".$row['listid']." THEN '".$querytwo["last"]."' ";
$trackingnumber_loop .= "WHEN ".$row['listid']." THEN '".$querytwo["tracking_number"]."' ";
$listids .= ", ".$row['listid'];
}
$listidsb = substr($listids, 2);
$querythree = "UPDATE list_data_extra
SET
id = CASE listid
".$id_loop."
END,
cid = CASE listid
".$cid_loop."
END,
first = CASE listid
".$first_loop."
END,
last = CASE listid
".$last_loop."
END,
tracking_number = CASE listid
".$trackingnumber_loop."
END
WHERE listid IN (".$listidsb.")";
mysql_query($querythree) or die(mysql_error());
}


Is there a better and faster way to update multiple columns in many records in one table with data from another table?

Answer

Unindexed JOIN and WHERE conditions can be slow, especially if they involve string data; try running these two (they make take a little time to run if the tables are large), and then trying your original query again.

ALTER TABLE list_data 
ADD INDEX serial_idx (serial_no);

ALTER TABLE list_data_extra 
ADD INDEX serial_idx (serial_no);