Query speed

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.

The question is caching. Database blocks gets cached after they are fetched from the disk and accessing them then is very quick.

Also select itself does not lock the table (I guess you’re using myISAM)- it is some concurrent updates what stall everything.

okey. Then we understand the magic :slight_smile:

Yes its myISAM. So would it runt faster if it was an innodb table?
I guess row level locking is better. Or do you have any other engine suggested?