Something is horribly wrong with your instance. Here is a comparable select from a database of mine.

mysql> select count(id) from r_links;
 -----------
| count(id) |
 -----------
|   2984572 |
 -----------
1 row in set (0.00 sec)

Table Type == MyISAM

mysql> desc r_links;
 ---------- ------------ ------ ----- --------- ----------------
| Field    | Type       | Null | Key | Default | Extra          |
 ---------- ------------ ------ ----- --------- ----------------
| id       | bigint(20) | NO   | PRI | NULL    | auto_increment |
| hn       | text       | NO   | MUL |         |                |
| sa       | text       | NO   | MUL |         |                |
| la       | text       | NO   | MUL |         |                |
| lp       | text       | NO   |     |         |                |
| ra       | text       | NO   | MUL |         |                |
| rp       | text       | NO   |     |         |                |
| state    | text       | NO   |     |         |                |
| datetime | text       | NO   |     |         |                |
 ---------- ------------ ------ ----- --------- ----------------
9 rows in set (0.00 sec)

You could be right Chris.

The system is a little heavily loaded, but even so I'd like it to be faster. I'm just a little unsure where to start..

If it's a frequent operation why not add some triggers to keep track of the number of rows.

Something is terribly wrong, the row count is stored in the meta data and the mysql query parser knows that.

Did you try count(1). I dont't remember exactly now, but I think it is supposed to be faster than count(*).
I am not sure that would solve your problem, since you are using count(id)

A collection of triggers might be a good solution. Thanks.

I'm going to tweak the memory settings a little first to see if that helps improve performance generally - it seems that many operations are taking longer than they should, but this is a pathological case.

Obviously something is screwed up but I'm not too familiar with these kind of problems...

Scott: COUNT(1) is about the same, and what I'm doing. I know that is faster than COUNT(*).

David - I didn't realize the row-count was generally available in the meta-data, though I guess I might have guessed, that suggests something more serious is wrong with my database setup.

If I remember right count(field) is slower compared to count(*) since the first one have to check that field is not null (so it will probably access the index and data) while the second one could avoid to access data. You should also "analyze table q_archive".
Would you try if anything changes in your case?

Giuseppe - I'm happy to try both ways. Once I've finished running " optimize table q_archive;" I'll post what the difference is.

mysql> select COUNT(id) FROM q_archive;
 ----------- 
| COUNT(id) |
 ----------- 
|   2752313 | 
 ----------- 
1 row in set (10.00 sec)


mysql> select COUNT(*) FROM q_archive;
 ---------- 
| COUNT(*) |
 ---------- 
|  2752598 | 
 ---------- 
1 row in set (7.73 sec)

I'm not sure if the speedup there is real though - it seems to be more a caching artifact.

Something is indeed seriously screwed up for a count to take that long.
Regarding your comment on using `cat sum', though, notice that you _can_ do something like that with a database; you only need to have a "static view", in more robust databases, or a real table that is updated from time to time, or by a trigger on every insert/delete, so no real gain in dropping the database for flat files there.

Already said, but more discussion in there : http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/

Hi Steve, I'm inferring that it's an InnoDB table, which means that MySQL has to scan the whole table to do the row count, whereas if it were MyISAM that meta-information is stored at all times. If this is important enough then ALTER the table to make it use the MyISAM storage engine, bearing in mind the trade-offs that that brings. Some extra detail: http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/ Rob

Rob: I've converted from InnoDB to MyISAM and the performance has just improved considerably.

I will have to read more about the pros/cons of the two table types, but thanks very much for giving me the prod.

(The table in question gets about 50,000 inserts 4 times an hour. But otherwise is queried N times a second - so read performance, and the COUNT(id) query is more important to me than then insert performance.)

Just bear in mind that whilst you INSERT with MyISAM your SELECT queries will block. Not the same with InnoDB. Various tweaks can be applied to improve that such as low_priority_updates and concurrent_insert.
Sounds like MyISAM is more your cup of tea though.

I went to a MySQL performance-hack related tech talk at Google once, and one thing I took away from it was that EXPLAIN SELECT COUNT(*) FROM foo; was significantly faster than SELECT COUNT(*) FROM foo;
I can't remember if it was as accurate though. Try it and see.