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.

  • Please show the query plan. Why aren't you parameterizing the queries? Nov 7 at 12:03
  • I don't know what I should be doing. Explain gave me this: 1;SIMPLE;comment;range;PRIMARY,id;id;4;NULL;1;Using where
    – Phoenixy
    Nov 7 at 14:38
  • Please consider reading this advice
    – mustaccio
    Nov 7 at 15:11
  • Please provide SHOW CREATE TABLE comment; and EXPLAIN SELECT ... and EXPLAIN UPDATE ... From those, we can probably deduce what the problem is and provide a workaround.
    – Rick James
    Nov 10 at 19:28


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.