Shatners Shatners - 2 years ago 124
SQL Question

MySQL query hangs on `SELECT COUNT(*)`

I've been playing around with MySQL and sqlalchemy to gather and store data. Over the the weekend I was collecting tweets at about 20,000 tweets/hour and placing them in a table

indexed by their tweet id. I am expecting ~1,000,000 rows, but when I run

SELECT COUNT(*) from raw_tweets;

the query just hangs. I've waited a few minutes and still nothing. My knowledge of sql is fairly limited. Is it possible there is a lock on the table? Would that cause it to hang without giving rise to an error? How can I go about diagnosing/fixing this problem?

P.S. it looks like all my queries on that table are hanging.

Answer Source

Most probably it doesn't hang but needs very much time to execute.

If the table engine is InnoDB, SELECT COUNT(*) must read all the rows from the table (in order to count them) and, if the database is under heavy use then the operation takes a lot of time.

This is documented in the Limits on InnoDB Tables page:

InnoDB does not keep an internal count of rows in a table because concurrent transactions might “see” different numbers of rows at the same time. To process a SELECT COUNT(*) FROM t statement, InnoDB scans an index of the table, which takes some time if the index is not entirely in the buffer pool. To get a fast count, you have to use a counter table you create yourself and let your application update it according to the inserts and deletes it does. If an approximate row count is sufficient, SHOW TABLE STATUS can be used.

As explained above, if an approximate row count is enough for you then run:


and look into the Rows column of the result.

Please note:

  • the number of rows returned by SHOW TABLE STATUS is approximate; it can be off of the real value by several percents (the difference is higher when the table is small);
  • the value returned by SHOW TABLE STATUS changes on each subsequent run, even if there is no write activity on the table.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download