I’m just qurious how mysql does in this case
SELECT COUNT(*) as iTotal FROM messages WHERE memberID = 1 AND read = ‘N’
(I know it’s better to create a new table that stores these values as columns, so we could grab them with simple selects queries. And we will change this query. I’m just interested for general knowledge)
So let’s now say we have a couple of millions of posts in the table, and a couple of thousand members. memberID is indexed (not together with read, and I guess that’s not a good idea to do because that column is updated quite a lot).
The first time a member signs in, the query in the worst cases take 20s (locking the table so other queries can’t run). I guess this is fair, a lot of posts and it have to count them one by one since we haven’t indexed read.
But my question is, now that the query has run, if we run the query again a minute later the query will not take a second. Even if the table is updated, so it shouldn’t be the query cache. Do mysql store sub results from the count before and use that?
Let say the member signs out and don’t return for a hour. Then the query will take 20s again.
