Count table rows

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?

|

miken32 · Accepted Answer · 2022-10-17 16:37:39Z

104

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, 2022 at 16:37

miken32

42.5k16 gold badges127 silver badges177 bronze badges

answered Sep 14, 2016 at 17:03

Nick

10.6k5 gold badges52 silver badges95 bronze badges

4 Comments

Chung Lun Yuen Over a year ago

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?

Nick Over a year ago

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 Over a year ago

I am not sure. but for Innodb is not real count. But is quite useful for million row tables.

th3penguinwhisperer Over a year ago

Going through tables to count each row to get a row count is rather ridiculous. So I prefer this answer as well.

Bryan · Accepted Answer · 2018-11-28 01:44:35Z

39

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, 2018 at 1:44

Bryan

2,85525 gold badges39 silver badges45 bronze badges

answered Aug 6, 2015 at 6:22

Santhosh Tangudu

7879 silver badges19 bronze badges

6 Comments

NaturalBornCamper Over a year ago

Is this faster as well on a MyISAM table since MyISAM is storing the number of rows?

Santhosh Tangudu Over a year ago

I never tested this on MyISAM.

Raymond Over a year ago

Why does someone want to see APPROXIMATE number of rows? WTF

th3morg Over a year ago

@Raymond It's reasonable that someone may just trying to get an estimate for the sake of decision making, but doesn't need an exact count. In my experience, this is a more common scenario in a production environment, than an exact number.

Rick Il Grande Over a year ago

@raymond when you work with big tables the exact number of rows is not a concern.

|

David Snabel-Caunt · Accepted Answer · 2009-12-12 13:34:07Z

6

Simply:

SELECT COUNT(*) FROM `tablename`

answered Dec 12, 2009 at 13:34

David Snabel-Caunt

58.4k13 gold badges118 silver badges132 bronze badges

Comments

Adriaan Stander · Accepted Answer · 2009-12-12 13:34:23Z

5

select count(*) from YourTable

answered Dec 12, 2009 at 13:34

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Comments

Yuseferi · Accepted Answer · 2018-06-13 12:54:08Z

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, 2018 at 12:54

Yuseferi

8,83011 gold badges77 silver badges106 bronze badges

2 Comments

jcoffland Over a year ago

This is incorrect. See stackoverflow.com/questions/5179969/… COUNT(1) may actually be much slower for MyISAM.

Yuseferi Over a year ago

@jcoffland you can try t by myself, especially when you have a join or where conditions is will be much much faster than count(*).

Hamboy75 · Accepted Answer · 2025-01-24 07:27:37Z

4

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

edited Jan 24 at 7:27

answered May 6, 2020 at 21:16

Hamboy75

1,1241 gold badge13 silver badges28 bronze badges

1 Comment

Softwared Over a year ago

This is brilliant! Counted rows for an unusable table.

Suanbit · Accepted Answer · 2018-03-27 09:39:10Z

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, 2018 at 9:39

Suanbit

4911 gold badge5 silver badges12 bronze badges

Comments

Mohammad Kanan · Accepted Answer · 2018-09-17 21:59:29Z

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, 2018 at 21:59

Mohammad Kanan

4,63210 gold badges25 silver badges50 bronze badges

2 Comments

jcoffland Over a year ago

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.

Mohammad Kanan Over a year ago

@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

Qirel · Accepted Answer · 2016-09-15 18:18:50Z

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, 2016 at 18:18

Qirel

26.6k7 gold badges54 silver badges71 bronze badges

answered Sep 14, 2016 at 16:49

alpc

6405 silver badges6 bronze badges

1 Comment

luke77 Over a year ago

This answer is outdated, mysql_result is deprecated in PHP 5.5.0 and removed in PHP 7.0.0

rashedcs · Accepted Answer · 2017-08-09 03:20:09Z

1

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

select count(*) from table_name;

answered Aug 9, 2017 at 3:20

rashedcs

3,7632 gold badges43 silver badges44 bronze badges

Comments

gayavat · Accepted Answer · 2021-01-02 02:02:42Z

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 2, 2021 at 2:02

gayavat

19.5k11 gold badges49 silver badges60 bronze badges

Collectives™ on Stack Overflow

Count table rows

12 Answers 12

9 Comments

4 Comments

6 Comments

Comments

Comments

2 Comments

1 Comment

Comments

2 Comments

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

12 Answers 12

9 Comments

4 Comments

6 Comments

Comments

Comments

2 Comments

1 Comment

Comments

2 Comments

1 Comment

Comments

Comments

Linked

Related