How to optimize an increment query in Laravel?

Tags: , ,



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.



Source: stackoverflow