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

Thank You

________________________________Iyonix

DarrenTeam Member

Joined: 05 Feb 2002
Posts: 549
Location: London

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

Code:

SELECT count(*) FROM tablename

drathbunWebHelper

Joined: 01 Mar 2003
Posts: 69
Location: Texas

Posted:
Sat Mar 01, 2003 10:12 pm (14 years, 9 months ago)

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. 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.

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

________________________________

Last edited by Daniel on Tue Mar 04, 2003 6:30 am, edited 1 time in total

drathbunWebHelper

Joined: 01 Mar 2003
Posts: 69
Location: Texas

Posted:
Tue Mar 04, 2003 2:52 am (14 years, 9 months ago)

Daniel wrote:

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

Why wouldn't you?

DanielTeam Member

Joined: 06 Jan 2002
Posts: 2564

Posted:
Tue Mar 04, 2003 6:30 am (14 years, 9 months ago)

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

________________________________

drathbunWebHelper

Joined: 01 Mar 2003
Posts: 69
Location: Texas

Posted:
Tue Mar 04, 2003 5:48 pm (14 years, 9 months ago)

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?

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?

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.

You cannot post new topics in this forum.You cannot reply to topics in this forum.You cannot edit your posts in this forum.You cannot delete your posts in this forum.You cannot vote in polls in this forum.