Can I combine both two update queries in MySQL to make it faster?

Right now I’m running both of these queries with PHP:

UPDATE members SET balance = balance - $cost WHERE id = '$member'");
UPDATE campaigns SET h_hits = h_hits + 1, 
                    d_hits = d_hits + 1, 
                    traffic = traffic + 1, 
                    spent = spent + $cost, 
                    timestamp = CURRENT_TIMESTAMP 
WHERE id = '$row_id'

I’ve seen a few questions about running multiple updates, but most of them seem to share the same WHERE statement whereas mine needs to use different ones :/

So here’s my two questions:

1. How would I combine these into a single query?
2. Most importantly, would it be faster to run this as a single query instead of as two separate ones?

Answer

You can combine them using CROSS JOIN, which doesn’t require any relationship between the rows in the two tables. You specify the criteria for each table in the WHERE clause.

UPDATE members AS m
CROSS JOIN campaigns AS c
SET m.balance = m.balance - $cost,
    c.h_hits = c.h_hits + 1, 
    c.d_hits = c.d_hits + 1, 
    c.traffic = c.traffic + 1, 
    c.spent = c.spent + $cost, 
    timestamp = CURRENT_TIMESTAMP 
WHERE c.id = '$member'
AND m.id = '$row_id'

Whether this is better than doing them as two queries is hard to predict, since it depends on a number of factors, such as the latency of the connection between the client and database server. If they’re on the same machine, I suspect not. You should benchmark the two methods to determine which is best in your situation.

If you’re making many of these queries, preparing the queries once and then executing them with different parameters should improve performance, since the query only has to be parsed when it’s prepared.