Results 1 to 4 of 4

Thread: Record count

  1. #1
    Join Date
    Sep 2003
    Posts
    22

    Unanswered: Record count

    What is the most efficient way to find the record count of a large table?

    Here are the two way's i've did it, would just like opinions on which one is better and more efficient. I'm coding PHP which shouldn't make a difference I'm just wondering about the SQL syntax.

    Code:
    select count(*) as myCount from myTable
    or
    Code:
    select * from myTable
    in the above after selecting all I use the mysql_num_rows function.

    Randy

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    The first returns only a single row (and column)

    The second returns the entire data from the table.

    Now think about which is more efficient...

  3. #3
    Join Date
    Sep 2003
    Posts
    22
    That's what I figured but I also figured it had to go through the whole table to get the count.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by RandyRiegel
    That's what I figured but I also figured it had to go through the whole table to get the count.
    no, it doesn't always do that -- myisam tables store the count, so getting the count is lightning fast

    but even if the database has to count all the rows, that's still a lot faster than exporting all the rows to let you count them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •