+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: CodeIgniter 4 (https://forum.codeigniter.com/forumdisplay.php?fid=28)
+--- Forum: CodeIgniter 4 Support (https://forum.codeigniter.com/forumdisplay.php?fid=30)
+--- Thread: Update and select a row in one database request (/showthread.php?tid=87905)
Update and select a row in one database request - Muzikant - 06-16-2023
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.
RE: Update and select a row in one database request - JustJohnQ - 06-16-2023
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.
RE: Update and select a row in one database request - Muzikant - 06-17-2023
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;RE: Update and select a row in one database request - JustJohnQ - 06-18-2023
I am convinced the stored procedure is faster than 2 queries, just curious how much faster? Did you run benchmarks?
RE: Update and select a row in one database request - Mni.day - 06-18-2023
(06-16-2023, 09:38 AM)Muzikant Wrote: Hi.
You run this queries in foreach section for every players?
RE: Update and select a row in one database request - Muzikant - 06-20-2023
(06-18-2023, 03:52 AM)JustJohnQ Wrote: Did you run benchmarks?
Not yet. But one minor advantage is saved bandwidth between servers.
(06-18-2023, 05:56 PM)Mni.day Wrote: You run this queries in foreach section for every players?
No. When player hits the score, then points will be added to him individually.
RE: Update and select a row in one database request - Mni.day - 06-20-2023
I'm sure the selection by primary key will take you about 0 ms. And you don't have to worry about two request
RE: Update and select a row in one database request - Muzikant - 06-22-2023
(06-20-2023, 06:33 PM)Mni.day Wrote: 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.