count(*) VS num_rows
View Single Post
04-03-2009, 03:22 PM
Join Date: Mar 2009
Location: Springfield, IL USA
This will count all rows in a table (grabbing the stored row count value if it can i.e. the table is MyISAM)
This is the same thing I said in this statement:
Using an asterisk (*) wildcards count() to consider all fields.
This will count all NOT NULL values for col (will grab the cached value if it can i.e. col is defined as NOT NULL therefore the optimizer will just do a COUNT(*) anyway I think)
This again, is the same thing I said here:
However, if you only need to get count totals from a specific field, you can use just that field_name and it serves the same purpose.
We're saying the same things, just in different ways. :)
SELECT COUNT(url) FROM `table_name` WHERE `category` = "some_category_name"
to get the total count of sites listed in a particular category within my site.
It's actually very useful because it helps me to locate urls (listings) that may have been orphaned during a category restructure (due to a category being moved, renamed, or eliminated).
I prefer to use MyISAM because I create indexes on specific fields which makes it a lot easier to locate records in a faster manner when offering different types of search (for my visitors) to locate records by title, category, index date, and so on.
@allworknoplay -- that's a good field to use to get a correct count if it's for a users db like you mention. It all depends on what the ID is for.
Example: I have a categories table with over 200,000 categories in it. Each one has a category #ID which is the primary field.
Then, there is another table which lists records indexed in each category and it too has an ID field.
In the categories table using count(ID) would tell me how many categories I have as there wouldn't be any null fields for this.
In the listing table, I don't use the count(ID) because some records that are inserted are ones that haven't been reviewed by an editor. I end up using two fields to get the active count for a category:
`url` and `queue`
If there are X-amount of `urls` in live `queue`, then I know how many records are active.
So, that said, it really depends on the data set you're trying to define.
The Following User Says Thank You to WebSavvy For This Useful Post:
View Public Profile
Send a private message to WebSavvy
Find More Posts by WebSavvy