SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast Igloo's Avatar
    Join Date
    Dec 2002
    Location
    Australia
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Which is faster?

    Hello,

    A very simple question to ask concerning both PHP and MySQL.

    If I were to use the following code in PHP, which of them would be the fastest to execute and return the result(s)?

    PHP Code:
    $query1_1 mysql_query("SELECT * FROM table");
    $query1_2 mysql_num_rows($query1_1);

    echo 
    $query1_2;

    $query2_1 mysql_fetch_array(mysql_query("SELECT COUNT(*) as counted FROM table"));
    $query2_2 $query2_1['counted'];

    echo 
    $query2_2
    As in, would a normal mysql_num_rows() be faster? Or would the MySQL function COUNT() be faster?

    And regarding that, would replacing the "*" (ALL) with a column reduce or the speed? Or Increase it, as it is only scanning one column?

    Thankyou,
    Igloo...

  2. #2
    Vermicious Knid moncur's Avatar
    Join Date
    May 2003
    Location
    Salt Lake City, UT
    Posts
    274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The COUNT() method will be much faster. You shouldn't use SELECT * unless you actually plan to read and use the result rows from the query.

    There are lots of comments on this in the PHP manual for mysql_num_rows:
    http://www.php.net/manual/en/functio...l-num-rows.php

  3. #3
    SitePoint Enthusiast Igloo's Avatar
    Join Date
    Dec 2002
    Location
    Australia
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, thankyou very much!

    I'll be using COUNT(column) then. Anything to reduce the load

    Igloo...

  4. #4
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi.

    I have read several times that COUNT(*) is faster than just using COUNT(columnname).

    Maybe check this with the experts in the DB forum.

  5. #5
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Chris82
    Hi.

    I have read several times that COUNT(*) is faster than just using COUNT(columnname).

    Maybe check this with the experts in the DB forum.
    It depends on the DBMS. There are conflicting opinions but no real proof which is faster. Read http://www.sql.nu/articles/count.shtml

    Anyway, it is better to do COUNTing based on what you actually want to count (i.e. base your actual query on functionality, and not efficiency, in this case) . Use COUNT(columnname) when you want to count the number of non-NULL columns in your table, and COUNT(*) when you want to count all the rows.

  6. #6
    SitePoint Guru prequel's Avatar
    Join Date
    Nov 1999
    Location
    Brisbane, Australia
    Posts
    682
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by moncur
    The COUNT() method will be much faster. You shouldn't use SELECT * unless you actually plan to read and use the result rows from the query.

    There are lots of comments on this in the PHP manual for mysql_num_rows:
    http://www.php.net/manual/en/functio...l-num-rows.php
    was working on writing a dvd collection database and finding the number of dvd titles in the database dvd table i timed both methods and count() was much faster on a small 101 record set (dvd titles) ~ 0.009 -0.014s vs 0.025-0.035s

  7. #7
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by moncur
    The COUNT() method will be much faster. You shouldn't use SELECT * unless you actually plan to read and use the result rows from the query.

    There are lots of comments on this in the PHP manual for mysql_num_rows:
    http://www.php.net/manual/en/functio...l-num-rows.php
    It actually depends on the table type you are using.

    By default, when using MyISAM based tables, the COUNT(*) from TABLE1 method will be faster since it never even has to access the actual table to get the result. MyISAM stores the number of rows in a control table that is accessed before any real table access is done.

    However, if you use INNODB style tables than the first method using mysql_num_rows() will be faster. In order for an INNODB based table to count the rows, it actually has to do a select on the table and then count them internally. There are acknowledged speed issues with this method and MySQL AB has no plans to rectify them because of the large amounts of resources it would require to change, test and implement. If they did fix the issue, it would most likely only be available to their corporate customers.
    Wayne Luke
    ------------



Bookmarks

Posting Permissions

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