1

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 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

Ilane
  • 484
  • 7
  • 18
  • you don't need to create an index on the id field, because you're not searching for rows based upon the id. Moreover it's the primary key, so it should already be indexed. Are there any applications/system running on the same server with the database? seems to me that it's a disk problem :p – ZaQ Jun 24 '11 at 23:16
  • @Zaq, @Ilane, correct - there is completely no need for index on primary key.. – Rihards Jun 24 '11 at 23:18
  • having bigint set to maybe 10 instead of 20 may speed it up, since it'll make the column and the table smaller. From what I understand InnoDb Count will still go through the whole table even though you don't have the WHERE clause, not just the id column. http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/ – AR. Jun 24 '11 at 23:21
  • The disk is actually a 1.6T mirrored RAID. There is a JBoss running on the same system, but uses another disk. – Ilane Jun 24 '11 at 23:45

1 Answers1

9

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.

Scott
  • 6,716
  • 9
  • 40
  • 46