count(*) VS num_rows
View Single Post
04-03-2009, 09:32 AM
Join Date: Oct 2007
Location: Manchester, UK
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.
This is not strictly true, both ways are totally different and have different meanings (and often different result sets!).
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 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:
mysql> SELECT * FROM `users` WHERE `users`.`clue` > 0;
Empty set (0.00 sec)
The Following User Says Thank You to sketchMedia For This Useful Post:
View Public Profile
Send a private message to sketchMedia
Visit sketchMedia's homepage!
Find More Posts by sketchMedia