Is there a way to set data in multiple records in one query?

[WetWired]

In MySQL, is there an efficient way to set a field to a specified list of values in a corresponding list of where conditions (based on the primary key), or do I have to do an individual UPDATE query for each record?

[Dean C]

Sorry you've totally lost me. Can you perhaps give an example of what you're trying to do :)?

[rake]

you could write a function to do that, but you'd still end up with more queries.

$list[0] = "...";
$list[0] = "...";

$where[0] = "...";
$where[1] = "...";

for($i = 0; $i < sizeof($list); $i++)
{
mysql_query("UPDATE table SET $list[$i] WHERE $where[$i]");
}

[WetWired]

you could write a function to do that, but you'd still end up with more queries.

$list[0] = "...";
$list[0] = "...";

$where[0] = "...";
$where[1] = "...";

for($i = 0; $i < sizeof($list); $i++)
{
mysql_query("UPDATE table SET $list[$i] WHERE $where[$i]");
}
Hmm. Is it more efficient (or even supported) to send multiple queries at the same time, separated by semicolons?

[Marco van Herwaarden]

With this loop you ARE sending multiple queries.

Better to do like Dean asked and give an examplke of what you try to do.

[rake]

With this loop you ARE sending multiple queries.

Better to do like Dean asked and give an examplke of what you try to do.

there's no way around sending multiple queries, if i understand correctly what wetwired wants to do.



Hmm. Is it more efficient (or even supported) to send multiple queries at the same time, separated by semicolons?


If you can send multiple queries in one go (never tried it), it would be more efficient than what i wrote, since you wouldn't lose time looping through that for statement.

[WetWired]

there's no way around sending multiple queries, if i understand correctly what wetwired wants to do.




If you can send multiple queries in one go (never tried it), it would be more efficient than what i wrote, since you wouldn't lose time looping through that for statement.
What I mean is that I would loop and accumulate the UPDATE queries in a string separated by semicolons, then send them all in the same mysql_query call. Does this do anything to minimize overhead?

Also, people are always talking about the cost of a hack in terms of queries, but I can easily construct a query that will take 10 times longer to execute than another. What gives? I hardly believe that the majority of users concerned with such things aren't using mysql as a library, so I don't understand why the number of queries should be more important than the time spent executing them.

[Dean C]

Hmmm, well by the sounds of things what you're doing doesn't sound very efficient either way. As I said before why not tell us exactly what you're trying to do then we can give tips. It's hard to offer advice without an example :)

[rake]

What I mean is that I would loop and accumulate the UPDATE queries in a string separated by semicolons, then send them all in the same mysql_query call. Does this do anything to minimize overhead?



I know that's what you meant. But i doubt it will have any impact.



Also, people are always talking about the cost of a hack in terms of queries, but I can easily construct a query that will take 10 times longer to execute than another. What gives? I hardly believe that the majority of users concerned with such things aren't using mysql as a library, so I don't understand why the number of queries should be more important than the time spent executing them.

EXACTLY. I'd rather have 10 fast queries than one big slow one. People generally worry too much about the query count.

[filburt1]

In MySQL, is there an efficient way to set a field to a specified list of values in a corresponding list of where conditions (based on the primary key), or do I have to do an individual UPDATE query for each record?
Look at REPLACE INTO syntax for updating if a primary key exists, inserting otherwise.

[WetWired]

<a href="https://vborg.vbsupport.ru/showthread.php?t=73145" target="_blank">https://vborg.vbsupport.ru/showthread.php?t=73145</a>
I'm trying to optimize this hack, as it seems the only replies I get are "too many queries". I could make it add only two queries per page by calculating activity every page load, but those would be big expensive queries, and I believe my current approach of doing the work once a day is better, but I can't get past the fact that it requires a query to store each result.

Right now, the first time a user's post is encountered on a given day, it does a query for the number of posts by the user for each of the x past days, and a query for the number of days visited out of the x past days, does some math, then does a query to store the dated result. The result is also cached so if the same user's post is encountered again on the same page, the calculation is not repeated.

Also, I'm considering adding a day field to each post to avoid doing math on dateline to determine the day for grouping, but I'm wondering if the performence enhancement is worth the extra hacking (not to mention the compatibility problems this may cause with other hacks that generate posts).