I have a very interesting issue. A super simple update takes about 400 ms. The website server waiting (TTFB) is usually around 150 ms, but with this update included it becomes 600 ms.
mysqli_query($conn,"UPDATE comment SET seen='".date('Y-m-d H:i:s')."' WHERE id='".$sub[0]."' LIMIT 1 ");
Here's the "sub query" before the update:
$sub = mysqli_fetch_array(mysqli_query($conn, "SELECT id, subscription FROM comment WHERE user_id='".$user2[0]."' and topic_id = 1 and deleted = 0 ORDER BY date DESC LIMIT 1"));
The above code gets the latest comment from the current user, then updates the date to the current time on that given comment. If I change the update query to update user_id, then it goes in an instant, but if I want to update the date, then it's slow. Going into phpmyadmin, the update query takes 0.0003 sec, so it's very quick.
Any idea what's going on here?
Also adding any indexes or not, doesn't affect performance. The comment table has about 32 000 rows. Using mariaDB 10.11.5.
SHOW CREATE TABLE comment;
andEXPLAIN SELECT ...
andEXPLAIN UPDATE ...
From those, we can probably deduce what the problem is and provide a workaround.