because the mysql manual says this:

COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause.

if i were have query with a WHERE clause, would it be faster/better to do this:

$q = "SELECT COUNT(*) FROM $source WHERE add_by_who = '$req_user'";
$result = mysql_query($q, $this->connection);
$row = mysql_fetch_row($result);
$num = $row[0];

OR this:

$q = "SELECT id FROM $source WHERE add_by_who = '$req_user'";
$result = mysql_query($q, $this->connection);
$num = mysql_num_rows($result);

(id is indexed)

any insights would be nice. thanks

    I would think that the larger the resulting number of matches would be, the faster the count() method would be relative to mysql_num_rows(), as in all cases count() only has one result row to return, while the other method returns one result row per match.

    But that is really just a theory on my part, as I do not have any empirical evidence to support it (and sometimes things like that perform contrary to what simple human logic would sugest).

      Why not try

      select count(id) from sometable where...

      Note that if id is null, it won't (or should I say shouldn't) get counted.

        It depends on what storage engine you have. Right below your quote it says

        This optimization applies only to MyISAM tables only, because an exact row count is stored for this storage engine and can be accessed very quickly. For transactional storage engines such as InnoDB, storing an exact row count is more problematic because multiple transactions may be occurring, each of which may affect the count.

        Anyway, the easiest way to know for sure is to try it out using [man]microtime[/man]. And don't forget to try Sxooter's suggestion as well, it is the one I think is the fastest.

          No, count() is always going to be faster than select a column. The MySQL docs refer to a specific optimisation which makes count even faster.

          You really want to create an index on the column you're using in the WHERE clause - this will speed it up dramatically in most cases**

          Mark

          ** It is impossible to generalise completely - your app is unique.

            thanks guys! the question was mainly out of intellectual curiosity, but i am using myisam on the project i plan on implementing it on. the app should be fairly low traffic and i don't expect too much data; but it always better to know, right?🆒

              Piranha wrote:

              IAnyway, the easiest way to know for sure is to try it out using [man]microtime[/man]. And don't forget to try Sxooter's suggestion as well, it is the one I think is the fastest.

              Not sure my way is faster, they both likely use an index, just depends on if you want to count nulls or not. count(*) will count nulls, and count(id) wont. If the id field is declared not null, then they're probably both about the same.

                If the id is null, and it is ment to be a primary key (which it normally is) then something is very wrong if it is null. Because of that I don't think this is an issue. But of course, on a table without a primary key it would matter. But then there is bigger issues to handle first.

                  Write a Reply...