count(*) VS num_rows
 

Hey guys, I think I read this somewhere else on this forum but I wanted to confirm. Do you all believe that this method is a better way to retrieve the number of rows and is quicker than using the mysql_num_rows function?



Also, I remember reading somewhere that if you want the count, you don't need to use the "*" symbol since it will read all columns, so instead just pick one column to get the count.

So would it be even better to use this statement?

I always use count Vs using num_rows. My site uses a lot of queries, and the load on the server is fairly light given the size of my site.

Using an asterisk (*) wildcards count() to consider all fields. It does add a very small (not even noticeable) lag in the query process. 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.

I guess it all comes down to personal preferences. Some programmers like to code it one way, and others another way.

When left between the choice of leaving the SQL server to process returned data and letting the script handle it, it is almost always a better idea to let the database do it. Using PHP to count the rows runs an unnecessary query which returns more rows than needed (the initial one), an another completely different one (num_rows runs another query to my understanding). Using the count function keeps it to one small consise query.

Thanks guys/gals:

So just to confirm, yes I only need the count, so I don't need any other data from the table. The code below sounds like the BEST and most efficient way to retrieve count...

Notice I am using "user_id" instead of "*"......


I am also using mysql_fetch_assoc instead of mysql_fetch_array since I don't need to return any array data although I'm not sure if in this situation, it really makes much of a difference.

The query looks good to me.

Out of interest, how many rows are you pulling from. I was able to pull COUNT(*) from a database at work that has 125,000 rows with about 50 columns (I did not deign this table) in less than one second.

50 columns? Is this mySQL? I thought that mysql had a limit of 32 columns per table?

Anyways, most of my tables are only a couple thousand rows long but I want to practice proper or GOOD coding techniques.

In this particular case though, my table has about 350,000 - 500,000 rows, it kinda depends because the data that gets inserted fluctuates, but 500K is about the max that I have seen with 350K as the min.

The number of columns isn't all that much, about 15 columns...

And I TRY to use the best column types although I will admit sometimes I get lazy and don't use the proper types...

For example I have a lot of "varchar" when they should be just "char" etc....

I use MS SQL at work, almost all our stuff is Microsoft.

MySQL's count function by far, its faster and less memory intensive (I know a COUNT isn't the biggest deal) but still since PHP needs to do the same check as your database server already have done.

Ok that explains it. I think with mysql, it is 32 columns. But whatever...or maybe what I read was that good coding practice was to keep it at 32? I know you mentioned you didn't design the table...

:-D

Sounds good. Each day goes by I try to pick up good coding practice. It's hard when you have bad habits for so long.




Here's another question for you guys that's kinda unrelated and I'm just throwing it out there.


Say you have 1,000,000 rows and you want the count.

And you have a regular flat file that has 1,000,000 lines.

Say both DB and flat file only has one type of data (one column).

Do you think the flat file will return faster via PHP or the DB will win out via SQL query...

The DB will almost always be faster because it uses advanced techniques you probably won't be programming into your app.

Hmmm very interesting, I thought that the flat file would have been faster just because it seems a bit more "raw" if you know what I mean. I'm not sure what code I would use but it would probably be fopen to open the file, and probably just a loop to count the lines.....

I wonder what the overhead is for that versus the DB....

Just a hypothetical question though, not that I am looking to replace my DB or anything....

If you are looking for a raw piece of text, it might be faster to search raw text, but it will without a doubt be more memory intensive because those functions tend to load it all into memory. If you are looking for anything more advanced than that, a database is probably your best bet.

You see, searching through raw text looks though every bit of it, SQL uses more advanced techniques so it doesn't have to do such a low level comparison.

Thanks, and you're right.

The SQL language alone offers many many commands that you can do easily whereas searching and performing functions on a text file you would basically be re-inventing the wheel somewhat...

This is not strictly true, both ways are totally different and have different meanings (and often different result sets!).

COUNT(col):
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)

COUNT(*):
This will count all rows in a table (grabbing the stored row count value if it can i.e. the table is MyISAM)

As for performance, it really depends on the circumstances and environment, for example if you have a MyISAM table MySQL will cache the table row count, then when you run COUNT(*) it will retrieve the cache, this also applies if you do this COUNT(col_1) and col_1 is defined as NOT NULL.

If for example you do a COUNT(col_2) and col_2 is defined as NULL (thus a value can be NULL) mysql cant just grab the row count (because its asking a different question, not number of rows but NOT NULL values from that column) it must do a full scan to grab how many NOT NULL's exist within that column which can result in a performance hit.

InnoDB on the other hand doesn't have a row cache value, thus it will scan the table anyway and it can be slow for COUNT(*) querys when the WHERE clause is omitted, however I don't know much about InnoDB I must be honenst.

For more information:

http://www.mysqlperformanceblog.com/...t-vs-countcol/
http://www.mysqlperformanceblog.com/...innodb-tables/

Yes, I use MySQL MyISAM and all my fields are set to NOT NULL due to the type of data that is required for the fields in my db.

Thanks Sketch!

I see your point.

When I do a count like: "select count(user_id) from users"

The column that I select is always the ID column that is autoincremented and unique to the table. This should assure me that I won't have to worry about any of the data being NOT NULL.

Do you see any issues with this?

This is the same thing I said in this statement:

This again, is the same thing I said here:

We're saying the same things, just in different ways. :)

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

Thanks Websavvy, that makes perfect sense to me. I too use myisam as my DB engine. I guess I probably should have mentioned that since a different engine could possibly behave differently...

I don't really know all that much about innodb anyways...

Yeah, I don't know anything about innodb either. Never cared to learn it either. I've been using MyISAM for 10 years now. I write all of my own code. My site doesn't use any 3rd party scripts.

I may not be the very best programmer in the world but I do pretty well for a person that is legally blind. I can see shapes, and colors, just not very well.

Wow good for you!!

I think I fall into that category as well, that's why I've been trying to become a better programmer with better techniques.

For just about 10 years, I've been able to get away with just coming up with code to make things work with a somewhat good understanding but never really a good deep understanding of the fundamentals. So now it is my mission to get to a point where everything is really clear to me.

I've read some of your posts here and you sound very very knowledgeable.....