Except if "id" is indexed as unique (such as a primary key), in which case COUNT(id) should be slightly faster than COUNT(*). At least in theory.

Sorry, but that is wrong on three counts (no pun intended.)

  1. Even if id is unique, there may be NULLs. And that depends on the RDBMS, where Oracle and mysql, do not store NULLs on the INDEX, you are correct that an index full scan is enough, however, in SQL Server, DB2, and other that do store NULL, there may be a NULL on the index which needs to be checked for. In any case, COUNT(*) may give a different number as those NULLs are counted.
  2. The primary key is not only unique, it is also not null, which means that it and COUNT(*) will return the same number. However, this requires reading the data dictionary to find that this is indeed the PK, and then reading in all the blocks if it isn't already in memory. Though, if the optimizer is smart enough, it won't bother doing this anyway and it will rewrite it to the equivalent of COUNT(*).
  3. COUNT(*) only needs a number, so if statistics were taken, it just needs to check the high water mark, an efficiency not available to counting columns.

Logically, nothing can be faster than COUNT(*) (or COUNT(1), etc...).

In reply to Re^3: Use of uninitialized value in lc by chacham
in thread Use of uninitialized value in lc by Alice Wonder

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.