Update and select a row in one database request

Hi. I have a MySQL database table players with columns id, name and points. I want to update points by incrementing them and select the affected row in one database request.

Example:

Code:

UPDATE players
SET points = points + 100
WHERE id = 1
LIMIT 1;

SELECT name, points
FROM players
WHERE id = 1
LIMIT 1;

I need it in one request, because there is an assumption, that tens of requests could run per second, so it needs to be really quick.

Is it possible to combine those queries into one, or is it possible to run them at once while still getting the result? If so, how to implement it with CodeIgniter 4, especially with query builder? Thank you for your tips.

I don’t think it is possible to make this faster in mySql. Oracle supports the ‘returning’ clause which could bring your code back to one request.

So far I achieved it by MySQL procedures. At first I needed to create a procedure on MySQL server.

I ran this query on the database server:

Code:

DELIMITER //
CREATE PROCEDURE add_points(IN player_id INT, IN points_num INT)
BEGIN
    UPDATE players SET points = points + points_num WHERE id = player_id LIMIT 1;
    SELECT name, points FROM players WHERE id = player_id LIMIT 1;
END //
DELIMITER ;

Second in my model I added method to call database procedure:

PHP Code:

// ...
public function addPoints($playerId, $pointsNum) {
    $sql = 'CALL add_points(' . (int) playerId . ', ' . (int) $pointsNum . ');';
    $query = $this->query($sql);
    return $query->getRowArray();
}
// ... 


Third from the controller I called a model method:

PHP Code:

// ...
$playerModel->addPoints(1, 100);
// ... 


---

Another useful database queries:

Code:

CALL add_points(1, 100);

Code:

DROP PROCEDURE add_points;

Code:

SHOW PROCEDURE STATUS;

I am convinced the stored procedure is faster than 2 queries, just curious how much faster? Did you run benchmarks?

Hi. 

You run this queries in foreach section for every players?

Did you run benchmarks?

Not yet. But one minor advantage is saved bandwidth between servers.

You run this queries in foreach section for every players?

No. When player hits the score, then points will be added to him individually.

I'm sure the selection by primary key will take you about 0 ms. And you don't have to worry about two request

I'm sure the selection by primary key will take you about 0 ms. And you don't have to worry about two request

It is two different things. There is a database server contacted from web server. In terms of count of requests, my solutions is 50 % more effective, as the number of requests is exactly one half.

Another thing is internal SQL selection and query running. When you look at procedure, then there is still two ID findings, so from this point of view it has no effect.

As far as I know, combining update and select into one query is not possible. If it will, then it could be called directly from web server in one query and MySQL procedure will not be needed at all. The point of my solution is to reduce number of requests from web server to database server.

---

If somebody have different approach to make one or both of this things more effective, then leave the comment please.