COUNT(id) query is taking too long, what performance enhancements might help?

I have a query timeout problem. When I did a:

SELECT COUNT(id) AS rowCount FROM infoTable;

in my program, my JDBC call timed out after 2.5 minutes.

I don't have much database admin expertise but开发者_C百科 I am currently tasked with supporting a legacy database. In this mysql database, there is an InnoDB table:

+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | bigint(20) | NO   | PRI | NULL    | auto_increment | 
| info  | longtext   | NO   |     |         |                | 
+-------+------------+------+-----+---------+----------------+

It currently has a high id of 5,192,540, which is the approximate number of rows in the table. Some of the info text is over 1M, some is very small. Around 3000 rows are added on a daily basis. Machine has loads of free disk space, but not a lot of extra memory. Rows are read and are occasionally modified but are rarely deleted, though I'm hoping to clean out some of the older data which is pretty much obsolete.

I tried the same query manually on a smaller test database which had 1,492,669 rows, installed on a similar machine with less disk space, and it took 9.19 seconds.

I tried the same query manually on an even smaller test database which had 98,629 rows and it took 3.85 seconds. I then added an index to id:

create index infoTable_idx on infoTable(id);

and the subsequent COUNT took 4.11 seconds, so it doesn't seem that adding an index would help in this case. (Just for kicks, I did the same on the aforementioned mid-sized db and access time increased from 9.2 to 9.3 seconds.)

Any idea how long a query like this should be taking? What is locked during this query? What happens if someone is adding data while my program is selecting?

Thanks for any advice, Ilane

You might try executing the following explain statement, might be a bit quicker:

mysql> EXPLAIN SELECT id FROM table;

That may or may not yield quicker results, look for the rows field.