Count table rows

Question

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

score 257 · Accepted Answer · answered Dec 12 '09 at 13:34

257

SELECT COUNT(*) FROM fooTable;

will count the number of rows in the table.

See the reference manual.

answered Dec 12 '09 at 13:34

Gregory Pakosz

69,011
20
139
164

8

Is it any faster when I use name of indexed column instead of * ? Like this: SELECT COUNT(id) FROM `tablename` – Nov 08 '12 at 21:01
1

Only slightly. I got a 21961904 row table which his COUNT queried in 115 sec, while using the ID took 107 sec. – Bondolin Jun 06 '13 at 11:44
2

COUNT(\*) is a strict language definition. You will get a parse error if you try COUNT (\*) note the space – ppostma1 Jun 23 '14 at 18:30
9

You can do `COUNT(1)`, this will be the fastest way. – Shota Papiashvili Sep 14 '16 at 17:05
What is the best way for using COUNT() to write a variable in PHP? Do I do "...COUNT(*) AS rowCount..." in the SQL, does it use $results->num_rows, or is there a way to call this result directly? – Nosajimiki Apr 24 '17 at 19:26
wouldn't it be more efficient or less computationally intensive to count a single column instead of ALL columns??? – oldboy Mar 30 '18 at 22:22
@Nosajimiki once you execute the prepared statement and fetch the results... `$result = $statement->fetch(PDO::FETCH_ASSOC);`... you can then call it by `$result["COUNT(*)"];` – oldboy Mar 30 '18 at 22:26
COUNT(*) function without a WHERE clause or additional columns, will perform very fast on MyISAM tables because the number of rows is stored in the table_rows column in the tables table of the information_schema database. For transactional storage engines such as InnoDB, storing an exact row count is problematic because InnoDB does not keep an internal count of rows in a table. Source: https://www.navicat.com/en/company/aboutus/blog/695-getting-row-counts-in-mysql-part-1 – Diana Oct 27 '19 at 18:21
InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference. Source: https://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html – Yusuf Çağlar Jan 26 '20 at 01:38

score 95 · Answer 2 · edited Oct 17 '22 at 16:37

95

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.

edited Oct 17 '22 at 16:37

miken32

42,008
16
111
154

answered Sep 14 '16 at 17:03

Nick

9,962
4
42
80

3

This looks like the most efficient way to count rows. I wonder why it is not the answer? I'm using InnoDB. With table lock, the number of rows should be exact right? – Chung Lun Yuen Oct 27 '16 at 14:07
for innodb it is **estimated**. but you can use it in some cases "Our website have xxx members", "We detected xxx results similar to yours" and so on. – Nick Oct 27 '16 at 14:13
I am not sure. but for Innodb is not real count. But is quite useful for million row tables. – Nick Oct 28 '16 at 01:01
4

Going through tables to count each row to get a row count is rather ridiculous. So I prefer this answer as well. – th3penguinwhisperer Jun 18 '17 at 21:05

score 38 · Answer 3 · edited Nov 28 '18 at 01:44

38

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

edited Nov 28 '18 at 01:44

Bryan

2,870
24
39
44

answered Aug 06 '15 at 06:22

Santhosh Tangudu

759
9
19

Is this faster as well on a MyISAM table since MyISAM is storing the number of rows? – NaturalBornCamper Mar 08 '17 at 11:06
I never tested this on MyISAM. – Santhosh Tangudu Jan 18 '18 at 12:42

score 6 · Answer 4 · answered Dec 12 '09 at 13:34

6

Simply:

SELECT COUNT(*) FROM `tablename`

answered Dec 12 '09 at 13:34

David Snabel-Caunt

57,804
13
114
132

score 5 · Answer 5 · answered Dec 12 '09 at 13:34

5

select count(*) from YourTable

answered Dec 12 '09 at 13:34

Adriaan Stander

162,879
31
289
284

score 4 · Answer 6 · answered Jun 13 '18 at 12:54

4

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;

answered Jun 13 '18 at 12:54

Yuseferi

7,931
11
67
103

1

This is incorrect. See https://stackoverflow.com/questions/5179969/what-is-better-in-mysql-count-or-count1 COUNT(1) may actually be much slower for MyISAM. – jcoffland Oct 25 '18 at 15:43
@jcoffland you can try t by myself, especially when you have a join or where conditions is will be much much faster than count(*). – Yuseferi Oct 26 '18 at 04:36

score 3 · Answer 7 · answered Mar 27 '18 at 09:39

3

Just do a

SELECT COUNT(*) FROM table;

You can specify conditions with a Where after that

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

answered Mar 27 '18 at 09:39

Suanbit

471
1
4
12

score 2 · Answer 8 · answered Sep 17 '18 at 21:59

2

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

answered Sep 17 '18 at 21:59

Mohammad Kanan

4,452
10
23
47

1

This may return an estimated number of rows. In one example, I got 55,940,343 rows using COUNT(*) but 56,163,339 using this method so it was off by more than 200k. – jcoffland Oct 25 '18 at 15:46
@jcoffland, I mentioned the answer by _ Santosh_ above which states the result is **approximate**. My answer is a more detailed practical query. Its quite clear across answers if you want precise count use `count(*)` with performance awareness – Mohammad Kanan Oct 25 '18 at 21:54

score 2 · Answer 9 · answered May 06 '20 at 21:16

2

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

answered May 06 '20 at 21:16

Hamboy75

938
1
11
22

1

This is brilliant! Counted rows for an unusable table. – Hassan Naqvi Jul 08 '21 at 12:00

score 1 · Answer 10 · edited Sep 15 '16 at 18:18

1

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

edited Sep 15 '16 at 18:18

Qirel

25,449
7
45
62

answered Sep 14 '16 at 16:49

alpc

598
3
6

This answer is outdated, mysql_result is deprecated in PHP 5.5.0 and removed in PHP 7.0.0 – luke77 Jun 01 '20 at 13:20

score 1 · Answer 11 · answered Aug 09 '17 at 03:20

1

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

select count(*) from table_name;

answered Aug 09 '17 at 03:20

rashedcs

3,588
2
39
40

score 0 · Answer 12 · answered Jan 02 '21 at 02:02

0

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

EXPLAIN SELECT * FROM table_name WHERE key < anything;

answered Jan 02 '21 at 02:02

gayavat

18,910
11
45
55

Count table rows

12 Answers12

Linked

Related