CODEWITHSUNDEEP
phpmysqlsql
Z0q
Z0q

Reputation: 1893

Simple SQL UPDATE Query slows down connection by 5-10 seconds

I am using a simple UPDATE query in my MySQL Server.

Normally, the SELECT Queries I am using are returning results in about in instant. However, after performing the UPDATE query once, the entire connection starts slowing down and from that moment on it takes about 5-10 seconds to load the results of the SELECT queries.

$time = time();
$stmt = $mysqli->stmt_init();

if( $stmt = $mysqli->prepare( "UPDATE users SET last_online = ? WHERE username = ?" ) )
{
    $stmt->bind_param( 'is', $time, $_POST['username'] );
    $stmt->execute();
}

$stmt->close();

Any ideas why this could be slowing down the connection?

P.S.: It also slows down connections of other users.

P.P.S: This update is executed once per 13 minutes per user.

Edit

The slowdown happens when executed directly from MySQL too.

Upvotes: 2

Views: 142

Answers (2)

Z0q
Z0q

Reputation: 1893

I found out the problem. One of the SELECT queries triggered another table in combination with the users table. That table contained massive data and used an ORDER BY .. DESC statement which was expensive. The problem is resolved now.

Maybe it had to update cache every time the UPDATE query was performed.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

One possibility is that you do not have an index on username. Then the query would require a full table scan, slowing things down. You can create such an index by doing:

create index idx_users_username on users(username);

Upvotes: 1

Related Questions