---
title: 'The innocent-looking query that slowed down our database'
source: 'https://youtube.com/watch?v=jxChblV3OdM'
video_id: 'jxChblV3OdM'
date: 2026-08-03
duration_sec: 843
---

# The innocent-looking query that slowed down our database

> Source: [The innocent-looking query that slowed down our database](https://youtube.com/watch?v=jxChblV3OdM)

## Summary

This video addresses a common performance bottleneck in PHP applications: slow UPDATE queries on a single timestamp column (like 'last seen') under high concurrency. Benjamin, a PHP performance expert, explains why such queries become slow and presents two solutions: a simple conditional update to reduce write frequency, and a more complex Redis-based approach for eventual consistency.

### Key Points

- **The Problem: Slow UPDATE on 'last seen'** [00:02] — An innocent-looking query 'UPDATE user SET last_seen = ? WHERE id = ?' became slow (over 1 second) under load, appearing ~100 times per hour in monitoring.
- **Root Cause: Row Lock Contention** [01:49] — InnoDB locks the row during updates; parallel Ajax requests updating the same user's 'last_seen' cause contention, leading to slow queries.
- **Solution 1: Conditional Update with Time Window** [04:10] — Instead of updating on every request, only update if the last update was more than 5 minutes ago. This reduces write frequency and contention.
- **Solution 2: Redis Sorted Set for Eventual Consistency** [06:38] — Use a Redis sorted set to store user IDs and timestamps. A cron job periodically syncs these to MySQL, reducing direct database writes.
- **Real-World Impact** [10:49] — After implementing the conditional update, slow queries dropped from ~140/hour to ~10-15/hour, and average latency improved.

### Conclusion

The video demonstrates that a simple conditional update can drastically reduce database contention, and a Redis-based approach offers a more scalable solution for eventual consistency. Both patterns are effective depending on accuracy requirements.

## Transcript

to track the access viewed or updated state of a database row in your PHP application? Chances are that was recently. The straightforward implementation will usually get you into scaling trouble once the application
grows with update statements slowing down a growing number of requests. In problem based on a real-world example in our own type of application and show two
possible patterns to solve this. Hi, I am Benjamin and I work on performance of PHP applications for more than 10 years helping thousands of developers along the way. Recently, a slow SQL query started appearing in our
monitoring, a query so innocent-looking that you would not expect it to be slow. Somewhere in a user listener on kernel request, the query was update user set last seen equals to some value where ID
last seen equals to some value where ID equals some ID value. So, in my book, this should be super fast because the access of this is based on ID. Um this is a MySQL database using InnoDB tables. It can immediately look up the row and
the finding of this uh the the finding of the row is super fast. So, the question would be why can this be just uh 1 second and something slow?
And why is this happening across various different transactions uh all the time? So, you can see every hour this query um becomes slow about 100
times. So, about 100 times this query takes longer than 100 milliseconds, usually much longer above 1 second or something like that. Why is this happening? So, the problem is that if you're
updating a row uh from many different places, then InnoDB will need to lock the row, need to wait for it to be written before other processes can access it. So, in our case, our application is um quite
parallel in the user interface, where you have multiple Ajax requests doing the same similar things. Especially um in every request, we are tracking when was this user last seen in the
application. And when this happens in parallel across for each other. And this is what makes this slow. So, how can we fix this problem? Because
usually in an application, if you have something like last seen or last updated or last viewed of, let's say, a blog post, um post, um a certain object, a product, um
lot of different things are require this kind of pattern, where you store a timestamp when something last happened to it. And if the application gets under load
more, this usually will come up as a bottleneck, and the update statements are just very slow. There are two patterns how we can solve this. So, I have um user listener object here. I copied
uh class from our existing Tag base code base and simplified it to focus just on this use case, that uh update user set last seen is called. So, this has absolutely no abstractions.
It's everything in one file, so we can focus on this single problem. We have a database connection. We have a security object. When the on kernel request, this is a Symfony event, it's fired in every request. When this is uh fired, the user
listener is called. It uh fetches the token from the security service. The token has access to the current user, and we are calling update user set last seen where ID uh equals this user ID and uh we use the
So, this is what uh leads to the problem we saw in our case. And this going to be slow. And there are two ways how we can solve this. So, I want to demonstrate two ways how you
I want to demonstrate two ways how you can solve this. The first way is if we don't actually need the last seen timestamp to be precise on the second, then why do we need to update it for every second? Let's say we are fine with
knowing that a user was last seen within, let's say, a block of 5 minutes. within, let's say, a block of 5 minutes. Then we can say
this and then we can check if 5 minutes ago is smaller than user get last seen.
Uh different way around. If 5 minutes ago is within when the user was last seen, then we stop this. So, this is a way where we don't have precise
numbers for this. Only every 5 minutes it gets updated. And this will usually completely remove the pressure on this one single column being getting updated from many different places at the same time. Especially if you have uh
Ajax-based concurrency that is happening in the application. You can even make it a little bit more um little bit better by avoiding over updating when multiple requests
essentially are fast enough to get across this check at the same time. And we avoid uhm, doing rights twice by doing rights twice by saying and last seen smaller equals this
and then 5 minutes ago. Types daytime and I need to check the logic again. Only update if last seen is smaller than 5 minutes ago. Yeah, that works.
So, if this statement is executed twice in two processes at the same time, only one of them will actually write and then this makes the performance better. Because every write needs to go to the disk and that makes
it slow. So, this way we have now uhm reduced the pressure on this column with a very very simple change and in my experience this is possible in most applications to do and will alleviate
the problem well enough. The second approach uses Redis. For this we're using a sorted set in Redis and sorted sets allow you to have a key and a score. And as scores we use
the timestamp and then Redis sorts the elements in the sorted set by the score. So, every new visitor sort of makes the high score going to the top. It's the last seen user and we have a list of all the last seen users. And then
after a while every 15 minutes, every 30 minutes or something like that, we have a cron job in the background that accesses all the users that seen the site and or have last visited the
site. And we iterate over them and update their timestamp in the MySQL database. This way we keep the existing data model where we have the last seen column on the user
object. But Uh, we are not automatically synchronizing that. It's sort of eventually consistent um after 15-30 minutes, whatever you choose. And there's also a trick in it. If somewhere in your user interface, you
If somewhere in your user interface, you actually need the up-to-date value, then you can directly call the synchronize service. So, do it before you read the values from the MySQL table. Or you can have a shortcut and read the value from
the sorted set. How do we implement this? So, we have a global key for user last seen. We have a service that gets Redis as a dependency. We have a method mark seen, and we would modify the user listener to call the
modify the user listener to call the mark seen method instead. So, user ID and timestamp now. And then in a cron job, we would call the synchronize method, and it works as following. We say, "Give me all the elements in the
We say, "Give me all the elements in the sorted set." So, um all the values that you have. And you say with scores. This is necessary so that you get the score, which is the timestamp. Then we iterate
over all the elements in the sorted set. And we keep the maximum timestamp. You will see later what it's for. And then we have the update statements that we already know here, where we set last seen um equals to this value.
And then we need to do uh two clean-up operations. So, we could just call this method at the end and completely delete the key. However, between reading the values here uh with ZRANGE and
performing all the updates, there were previous there were probably new values put into the key. And these new values need to stay there. So, we um get all the keys uh up to this maximum timestamp,
and then we delete all those keys, and the updated ones stay in the set in this case. So, that were two solutions to the problem of high pressure on a single timestamp column where updates were
timestamp column where updates were executed over and over again, increasing the sort of slowness of this request. Usually, it's not slow across all the updates because some users they are not as active, and then the row is maybe not
as contentious, and updates are going through easily. However, under pressure, you will see that the tail latency of a specific request or endpoint will grow.
That means there will be a larger and larger number of slow requests, and the number of fast requests will become fewer and fewer. So, over time, you see MySQL expanding more and more until at
maybe at some point every request updating the timestamp is going to be slow. So, this is going to be a pattern that slowly emerges once the application grows, and you need to be aware of this and see this over time. So, how did
these changes end up changing our application? >> Going back to the slow SQL item in Tightways, we can see this happening uh across the last 5 days regularly. I
would say like the maximum is like 140 times uh within 1 hour, and that is quite a lot. So, I rolled out a change for this um with a new query that also uses the
pattern where we filter. So, let me show you again. We are adding this. So, Tightways now has a new
query for this in the slow lock. Tightbase has a new query for this in the slow lock. We can see here, this is the query and last seen, and we still have queries that are slow in this case. It appeared 81 times
It appeared 81 times in the last So, it's about 10 to 15 times every hour, which is 1/10
10 to 15 times every hour, which is 1/10 of the amount that we had before. So, it significantly reduced the amount of queries that had requests that had very slow queries. And we can iterate through this here
by appearances, and what I've haven't seen before is that there are also a lot more of these queries that are not above 1 second, but around 200 milliseconds. So, it's not only that we
have fewer of those queries, but they're also on average faster than before. I hope you found this interesting to see that there is one extremely simple pattern to get around this contentious
queries. Usually, you will solve your problems this way and can go to the next topic in a few lines of code. And there's a more complex solution using Redis, but it's
also simple in a way that it allows you to stay working with the user last seen property or any timestamp property you use. You only need to handle and think of that it's only updated every once in a while from the Redis
set. Still, most of the code will be able to work with this if you don't need this these timestamps to be extremely accurate. From a user interface perspective, you can always use words and sentences like
was last was seen in the last 15 minutes was seen in the last hour instead of having to say last last seen in the last minute or seen a few seconds ago. So, in
my experience, most use cases don't require you to be second exact on these values and this is a good way to solve this. If you like this PHP content, please subscribe to
the channel and our newsletter to get informed of new videos for different PHP performance topics. See you.