I have to loop through 9600 DISTINCT Domains in query 1 and Update an id in a second table in Query 2

Question

The query takes forever to update all Domains in the second table.

Maybe there is a more efficient way to approach this?

$mysqli = new mysqli("localhost","xxxx","xxxx","xxx");

if ($mysqli -> connect_errno) {
  echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
  exit();
}

// Perform queries
$result = $mysqli->query(SELECT DISTINCT dom FROM pmcts_mt_pipeline_details);

$rows = $result->fetch_all(MYSQLI_ASSOC);
foreach ($rows as $row) {
$dom = $row['dom'];
$mysqli->query("UPDATE pmcts_email_marketing_companies_pharma_all_unique SET id_ind_mailer=1 WHERE email_dom='$dom' AND (mailerdaemon=0 AND mail_excluded=0 AND unsubscribe=0 AND changeto=0)");
}

What have you tried to resolve the problem? Why do you need to read all columns and run a seperate `UPDATE` query? Why not do all that directly with one query? — Nico Haase, Jun 23 '21 at 09:18
It seems that an [UPDATE from SELECT](https://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query) should solve your problem. — Gabriel Heming, Jun 23 '21 at 09:38
@NicoHaase: I Used the tip from Martijn. I Only used the suggestion with a small modification. Not using the foreach Loop which was the cause for slowing it down drasticly. — Jackwiz007, Jun 23 '21 at 16:55

Martijn · Accepted Answer · 2021-06-23T10:42:49.030

0

It's likely not the query that's slow, but the loop. Looping in PHP is slow.

You want to update one value to all matching rows, you might be able to use IN():

$result = $mysqli->query(SELECT DISTINCT dom FROM pmcts_mt_pipeline_details);
$rows = $result->fetch_all(MYSQLI_ASSOC);
$doms = array_column($rows, 'dom'); // *
$domsString = implode("','", $doms)

^{* Im not 100% about the array column, but the goals is to get all values-only in an array}

UPDATE pmcts_email_marketing_companies_pharma_all_unique 
SET id_ind_mailer=1 
WHERE email_dom IN ('".$domsString."') 
   AND (
      mailerdaemon=0 
      AND mail_excluded=0 
      AND unsubscribe=0
      AND changeto=0
   )

Note: Im assuming you are in control of the $dom-values and that they're clean and safe for mysql. If they're able to be manipulated by user input, please make sure you sanatize, or even better: use prepared statements.

edited Jun 23 '21 at 10:42

answered Jun 23 '21 at 09:07

Martijn

15,791
4
36
68

I hope that none of the `dom` values contains a quote.... – Nico Haase Jun 23 '21 at 09:20
1

Been working with proper frameworks for a while, manual sanatision slips my mind sometimes. I've added it to my answer – Martijn Jun 23 '21 at 09:30
1

Thanks a lot Martjin, very useful. The only thing I had to change is: ``` $doms = array_column($rows, 'dom'); Then it worked fine and fast. ``` – Jackwiz007 Jun 23 '21 at 09:38
The dom values are save – Jackwiz007 Jun 23 '21 at 09:41

I have to loop through 9600 DISTINCT Domains in query 1 and Update an id in a second table in Query 2

1 Answers1