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
SELECT COUNT(*) from raw_tweets;
Most probably it doesn't hang but needs very much time to execute.
If the table engine is
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 tstatement, 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 STATUScan be used.
As explained above, if an approximate row count is enough for you then run:
SHOW TABLE STATUS WHERE NAME = 'raw_tweets'
and look into the
Rows column of the result.
SHOW TABLE STATUSis approximate; it can be off of the real value by several percents (the difference is higher when the table is small);
SHOW TABLE STATUSchanges on each subsequent run, even if there is no write activity on the table.