Reputation: 2101

Count table rows

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

Upvotes: 191

Answers (12)

Hamboy75

Reputation: 1079

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 went from 0.4s to 0.0001ms

Upvotes: 4

Nick

Reputation: 10539

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.

The number shown is estimated for InnoDB and TokuDB but 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.

Upvotes: 104

gayavat

Reputation: 19398

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

EXPLAIN SELECT * FROM table_name WHERE key < anything;

Upvotes: 0

Santhosh Tangudu

Reputation: 787

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

Upvotes: 39

Mohammad Kanan

Reputation: 4582

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

Upvotes: 2

Yuseferi

Reputation: 8670

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;

Upvotes: 4

Suanbit

Reputation: 491

Just do a

SELECT COUNT(*) FROM table;

You can specify conditions with a Where after that

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

Upvotes: 3

rashedcs

Reputation: 3725

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

select count(*) from table_name;

Upvotes: 1

alpc

Reputation: 610

$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;`

Upvotes: 1