Get number of rows in MySQL

Question

Possible Duplicate:
Fastest way to count exact number of rows in a very large table?

What query do I use to get the number of rows that share a common specification.
Example: The number of rows where idpost = 3

possible duplicate of [Fastest way to count exact number of rows in a very large table?](http://stackoverflow.com/questions/6069237/fastest-way-to-count-exact-number-of-rows-in-a-very-large-table) or [php: count the number of rows in mysql?](http://stackoverflow.com/questions/7049153/count-the-number-of-rows-in-mysql) — Anirudh Ramanathan, Sep 08 '12 at 18:30

score 6 · Answer 1 · edited May 23 '17 at 12:18

6

You can use COUNT() documented here.

SELECT COUNT(1) FROM posts WHERE idpost = 3

EDIT: Updated according to dbf's suggestion.. Make sure you distinguish between COUNT(*) and COUNT(1), discussed here.

edited May 23 '17 at 12:18

Community

answered Sep 08 '12 at 18:28

Michal Klouda

2

for performance reasons if a lot of fields exist in the table, I would consider using COUNT(idpost) instead of * (all) – dbf Sep 08 '12 at 18:31
3

Both the accepted answer to that linked question (which is specific to [tag:sql-server], not [tag:mysql]) and the manual entry for [`COUNT()`](http://dev.mysql.com/doc//en/group-by-functions.html#function_count) make clear that `COUNT(*)` is perfectly efficient. The MySQL manual states: "*`COUNT(*)` is optimized to return very quickly if the `SELECT` retrieves from one table, no other columns are retrieved, and there is no `WHERE` clause.*" – eggyal Sep 08 '12 at 19:24
1

@dbf: You are incorrect. See this: [What is better in MYSQL count(*) or count(1)?](http://stackoverflow.com/a/5180023/548696). Both `COUNT(*)` and `COUNT(1)` **perform exactly the same**. – Tadeck Sep 08 '12 at 21:27

score 2 · Answer 2 · edited May 23 '17 at 12:26

2

The query looks like this:

SELECT count(*) FROM some_table WHERE (conditions);

In your example:

SELECT count(*) FROM some_table WHERE idpost=3;

EDIT:

If you were wondering, which way of counting all rows is better (count(*) or count(1)), see this: What is better in MYSQL count(*) or count(1)?.

edited May 23 '17 at 12:26

Community

answered Sep 08 '12 at 18:30

Tadeck

JvdBerg · Answer 3 · 2012-09-08T18:31:25.583

1

select count(*) from mytable where idpost=3

edited Sep 08 '12 at 18:31

answered Sep 08 '12 at 18:28

JvdBerg

Please don't link to w3schools, for more information, see [W3Fools](http://w3fools.com/) – John V. Sep 08 '12 at 18:39
What a piece of crap! A random bump from the street that writes a html page! – JvdBerg Sep 08 '12 at 18:41
Not quite random bumps, if you visit the page you'll notice the list of authors. – John V. Sep 08 '12 at 19:27

score 1 · Answer 4 · answered Sep 08 '12 at 18:29

1

Try

SELECT
    COUNT(*) as NumberRows
FROM
    your_table_name_here
WHERE
    idpost = 3
;

answered Sep 08 '12 at 18:29

Jost

score 0 · Answer 5 · answered Sep 08 '12 at 18:30

0

the select count(*) function grabs the number of rows that meet a certain criteria - define that in the where statement.

answered Sep 08 '12 at 18:30

tehdoommarine

5 Answers5