[mysql] Count table rows

What is the MySQL command to retrieve the count of records in a table?

You have to use count() returns the number of rows that matches a specified criteria

select count(*) from table_name;

If you have a primary key or a unique key/index, the faster method possible (Tested with 4 millions row tables)

SHOW INDEXES FROM "database.tablename" WHERE Key_Name=\"PRIMARY\"

and then get cardinality field (it is close to instant)

Times where from 0.4s to 0.0001ms

select count(*) from YourTable

If you have several fields in your table and your table is huge, it's better DO NOT USE * because of it load all fields to memory and using the following will have better performance

SELECT COUNT(1) FROM fooTable;

Simply:

SELECT COUNT(*) FROM `tablename`

Just do a

SELECT COUNT(*) FROM table;

You can specify conditions with a Where after that

SELECT COUNT(*) FROM table WHERE eye_color='brown';

As mentioned by Santosh, I think this query is suitably fast, while not querying all the table.

To return integer result of number of data records, for a specific tablename in a particular database:

select TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA = 'database' 
AND table_name='tablename';

It can be convenient to select count with filter by indexed field. Try this

EXPLAIN SELECT * FROM table_name WHERE key < anything; 

$sql="SELECT count(*) as toplam FROM wp_postmeta WHERE meta_key='ICERIK' AND post_id=".$id;
$total = 0;
$sqls = mysql_query($sql,$conn);
if ( $sqls ) {
    $total = mysql_result($sqls, 0);
};
echo "Total:".$total;`

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

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.