How many rows?

Posted: Sat Feb 08, 2003 2:01 pm (21 years, 2 months ago)

How would I find out how many rows are in a DB table? (What is the mySQL code to do this.)

Thank You

Posted: Sat Feb 08, 2003 2:24 pm (21 years, 2 months ago)

Code:

SELECT count(*) FROM tablename

WebHelper Joined: 01 Mar 2003 Posts: 69 Location: Texas

Depending on your database optimizer, the following can be substantially faster:

Code:

select count(primary_key) from table

Of course your table has to have a primary key (it should anyway) and you have to know what it is. Wink

But the syntax given in the previous answer is perfectly valid.

The reason the option I gave is sometimes faster, is that older databases would retrieve * in order to count * from the table. That means that you read the entire row just to count it. If you count a primary key, you are counting the index instead of the table.

Team Member Joined: 06 Jan 2002 Posts: 2564

Sorry to butt in here; I guess this is the same as what drathbun is suggesting, but this is what I use:

Code:

SELECT count(id) FROM tablename

However that would only work if you have an "id" column Very Happy

WebHelper Joined: 01 Mar 2003 Posts: 69 Location: Texas

Daniel wrote:

However that would only work if you have an "id" column Very Happy

Why wouldn't you? Shocked

Team Member Joined: 06 Jan 2002 Posts: 2564

Well if you're counting the rows in the "id" column, and there is no "id" column, then it can't work, can it? Razz

WebHelper Joined: 01 Mar 2003 Posts: 69 Location: Texas

Daniel wrote:

Well if you're counting the rows in the "id" column, and there is no "id" column, then it can't work, can it? Razz

My point is that you woudn't have a very good database design if you didn't have a unique key. By "id" I am assuming we're talking about a key. Something like, oh, I don't know, forum_id, topic_id, or post_id? Smile

You can count combinations of values as well, if your table has a combination (or concatenated) key. That's what I was trying to get at. Wink

Dave