Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Location
    So. Cal. USA
    Posts
    142

    Unanswered: PHP/MySQL Multiple SELECTs vs. Array Looping

    Hi, folks! I'm setting up a web page to show various counts of items from a MySQL db and was wondering if it is generally more effecient to peform about 20 different SELECTs each followed with a mysql_num_rows to get a count of results or if it's better to do a single SELECT of all records, load the data into an array, and then loop through the array to perform incremental counts. A simplified version of what I'm doing might be something like...

    // Do this 20+ times...
    $sql = "SELECT class, gender FROM students WHERE class='freshman' AND gender='male' "
    $result = mysql_query($sql, $link);
    $count_fm = mysql_num_rows($result);

    OR

    // Do this once and then loop...
    $sql = "SELECT class, gender FROM students";
    $result = mysql_query($sql, $link);
    while ($allstudents = mysql_fetch_array($result))
    {
    // Loop through result set or array and check for various conditions while incrementing counters... something like...
    // if ($class = 'freshman' AND $gender = 'male') { $count_fm++; }
    }

    Something similar to what I'm working on might output something like...

    TOTAL STUDENTS: 5000
    Freshman: 1200 male, 1400 female
    Sophomore: 1100 male, 1300 female
    ...
    Senior: 900 male, 950 female


    I'm not sure how significant it is, but one observation I'd note is that in the multiple SELECT solution, I don't actually need to grab any data or use mysql_fetch array()... just get the counts. In the single SELECT solution, however, I must use mysql_fetch_array() and use the data to properly increment my counters.

    Does anyone know if it's ALWAYS better to do one or the other or if its dependent on the number of records/columns involved and switches for big/small data sets?

    Thanxalot!
    --ST

  2. #2
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Does anyone know if it's ALWAYS better to do one or the other or if its dependent on the number of records/columns involved and switches for big/small data sets?
    Weird...I was just testing something similar to this last night...

    Anyways, long story short...if you're not actually using the data and only want to know how many records are in your query, use SELECT COUNT(field) FROM...

    This is almost always the fastest way. If you're trying to get different categories (ie, male & freshman, femal & sophmore) use GROUP BY to help you out.

    I think something like this would work:
    Code:
    SELECT class, gender, COUNT(*) as number 
    FROM students 
    GROUP BY class, gender

Posting Permissions

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