How to optimize an increment query in Laravel?

in a Laravel project I am incrementing views based on session I am using it like this:

$post = Post::whereSlug($slug)->firstOrFail();

$postKey = 'post_'.$post->id;

if (!Session::has($postKey)) {
    $post->increment('views');
    Session::put($postKey, 1);
}

Is there any way to optimize this? I have activated the slow queries log and I am getting results like this one:

# Time: 2021-04-03T12:05:20.992056Z
# User@Host: user[user] @ localhost [127.0.0.1]  Id: 168408
# Query_time: 2.843047  Lock_time: 0.000029 Rows_sent: 0  Rows_examined: 1;
update `posts` set `views` = `views` + 1, `posts`.`updated_at` = '2021-04-03 12:05:18' where `id` = 163518;

How to optimize this? Any suggestions?

Edit: Adding a manual EXPLAIN update statement:

EXPLAIN update `posts` set `views` = `views` + 1, `posts`.`updated_at` = '2021-04-03 12:05:18' where `id` = 163518;

Result: 1 row in set (0.01 sec)

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	UPDATE	posts	NULL	range	PRIMARY	PRIMARY	4	const	1	100.00	Using where

Answer

Don’t use ENGINE=MyISAM; switch to InnoDB.
Beware of other queries (in other connections) that are touching this table for a long time.

Advertisement

Answer