4WebHelp
 FAQ  •  Search  •  User Groups  •  Forum Admins  •  Smilies List  •  Statistics  •  Rules   •  Login   •  Register
Toggle Navigation Menu

 How many rows?
Post New TopicReply to Topic
View Previous Topic Print this topic View Next Topic
Author Message
Iyonix
WebHelper
WebHelper


Joined: 12 Nov 2002
Posts: 82
Location: Yarm, England

PostPosted: Sat Feb 08, 2003 2:01 pm (14 years, 10 months ago) Reply with QuoteBack to Top

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

Thank You

________________________________
Iyonix
OfflineView User's ProfileFind all posts by IyonixSend Personal Message
Darren
Team Member



Joined: 05 Feb 2002
Posts: 549
Location: London

PostPosted: Sat Feb 08, 2003 2:24 pm (14 years, 10 months ago) Reply with QuoteBack to Top

Code:
SELECT count(*) FROM tablename
OfflineView User's ProfileFind all posts by DarrenSend Personal MessageVisit Poster's Website
drathbun
WebHelper
WebHelper


Joined: 01 Mar 2003
Posts: 69
Location: Texas

PostPosted: Sat Mar 01, 2003 10:12 pm (14 years, 9 months ago) Reply with QuoteBack to Top

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.

________________________________
Dave
Photography Site :: Query Tools Forum :: Weekend Fun
OfflineView User's ProfileFind all posts by drathbunSend Personal MessageVisit Poster's Website
Daniel
Team Member



Joined: 06 Jan 2002
Posts: 2564

PostPosted: Sun Mar 02, 2003 7:41 am (14 years, 9 months ago) Reply with QuoteBack to Top

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

________________________________
Image

Last edited by Daniel on Tue Mar 04, 2003 6:30 am, edited 1 time in total
OfflineView User's ProfileFind all posts by DanielSend Personal Message
drathbun
WebHelper
WebHelper


Joined: 01 Mar 2003
Posts: 69
Location: Texas

PostPosted: Tue Mar 04, 2003 2:52 am (14 years, 9 months ago) Reply with QuoteBack to Top

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

Why wouldn't you? Shocked
OfflineView User's ProfileFind all posts by drathbunSend Personal MessageVisit Poster's Website
Daniel
Team Member



Joined: 06 Jan 2002
Posts: 2564

PostPosted: Tue Mar 04, 2003 6:30 am (14 years, 9 months ago) Reply with QuoteBack to Top

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

________________________________
Image
OfflineView User's ProfileFind all posts by DanielSend Personal Message
drathbun
WebHelper
WebHelper


Joined: 01 Mar 2003
Posts: 69
Location: Texas

PostPosted: Tue Mar 04, 2003 5:48 pm (14 years, 9 months ago) Reply with QuoteBack to Top

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

________________________________
Dave
Photography Site :: Query Tools Forum :: Weekend Fun
OfflineView User's ProfileFind all posts by drathbunSend Personal MessageVisit Poster's Website
Display posts from previous:      
Post New TopicReply to Topic
View Previous Topic Print this topic View Next Topic


 Jump to:   




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.


Page generation time: 0.057914 seconds :: 17 queries executed :: All Times are GMT
Powered by phpBB 2.0 © 2001, 2002 phpBB Group :: Based on an FI Theme