Count table rows

Solution 1:

SELECT COUNT(*) FROM fooTable;

will count the number of rows in the table.

See the reference manual.

Solution 2:

Because nobody mentioned it:

show table status;

lists all tables along with some additional information, including estimated rows for each table. This is what phpMyAdmin is using for its database page.

This information is available in MySQL 4, probably in MySQL 3.23 too - long time prior information schema database.

UPDATE:

Because there was down-vote, I want to clarify that the number shown is estimated for InnoDB and TokuDB and it is absolutely correct for MyISAM and Aria (Maria) storage engines.

Per the documentation:

The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

This also is fastest way to see the row count on MySQL, because query like:

select count(*) from table;

Doing full table scan what could be very expensive operation that might take hours on large high load server. It also increase disk I/O.

The same operation might block the table for inserts and updates - this happen only on exotic storage engines.

InnoDB and TokuDB are OK with table lock, but need full table scan.

Solution 3:

We have another way to find out the number of rows in a table without running a select query on that table.

Every MySQL instance has information_schema database. If you run the following query, it will give complete details about the table including the approximate number of rows in that table.

select * from information_schema.TABLES where table_name = 'table_name'\G

Solution 4:

Simply:

SELECT COUNT(*) FROM `tablename`

Solution 5:

select count(*) from YourTable