Way to improve or eliminate a mysql COUNT

问题:

Forgive my ignorance on this question, as I don't have much experience in this area. I have a page that uses a mysql COUNT(*) query to return a user's stats. For example, something like:

SELECT COUNT(*) FROM worker WHERE worker_id = '1234';

It uses the index for worker_id and checking out the EXPLAIN seems that the query looks good. However, this performs absolutely brutally, and can take 5+ seconds for a single query on a table with a million+ rows. Additionally, this is quite a common query, and sometimes it will result in a mysql timeout error with multiple queries trying to run this.

What would be a good way to 'solve' this issue? The information changes quite rapidly so I don't think caching it would work. How would I go about fixing this, and what do some large sites do the show stats (for example, Facebook Friends or Twitter Followers)?

回答1:

First, be sure that you have an index on worker(worker_id).

Second, if the column is really a number and not a string, then don't use single quotes:

SELECT COUNT(*)
FROM worker
WHERE worker_id = 1234;

Third, if you just need to know if the worker exists (and don't actually need the count), then run a query like this:

select 1
from worker
where worker_id = 1234
limit 1;

And check to see if any rows are returned.