SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    how can i count number of rows for several tables in one efficient query?

    i need to count number of rows for 4 diff table,

    Code PHP:
     
    $sql = mysql_query("SELECT userid FROM table_1 WHERE userid='$member_id' AND foo=1"); 
    $total_table_1_row = mysql_num_rows($sql);
     
    $sql = mysql_query("SELECT userid FROM table_2 WHERE userid='$member_id' AND foo=1"); 
    $total_table_2_row = mysql_num_rows($sql);
     
    $sql = mysql_query("SELECT userid FROM table_3 WHERE userid='$member_id'");
    $total_table_3_row = mysql_num_rows($sql);
     
    $sql = mysql_query("SELECT name FROM table_4 WHERE name='$member_name'");
    $total_table_4_row = mysql_num_rows($sql);

    it is lightning fast for 1 or 2 rows while testing on localhost, but what if i have millions of rows for each table?

    how can i combine them, with UNION?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    first of all, the way to count rows is with the COUNT(*) aggregate function in the database, not by retrieving all the rows in the tables, shipping them down the pipe to php, and counting them there

    yes, you would use UNION
    Code:
    SELECT SUM(subtotal) AS total
      FROM ( SELECT COUNT(*) AS subtotal
               FROM table_1 
              WHERE userid = $member_id 
                AND foo = 1
             UNION ALL
             SELECT COUNT(*)
               FROM table_2 
              WHERE userid = $member_id 
                AND foo = 1
             UNION ALL
             SELECT COUNT(*)
               FROM table_3 
              WHERE userid = $member_id 
             UNION ALL
             SELECT COUNT(*)
               FROM table_4 
              WHERE name = '$member_name' ) AS u
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    sorry but i not sure how UNION works, your example gives me the total number of rows for ALL 4 tables combined, not number of rows for each table,

    Code PHP:
    $query = "SELECT SUM(subtotal) AS total
      FROM ( SELECT COUNT(*) AS subtotal
               FROM table_1 
              WHERE userid = $member_id 
                AND foo = 1
             UNION ALL
             SELECT COUNT(*)
               FROM table_2 
              WHERE userid = $member_id 
                AND foo = 1
             UNION ALL
             SELECT COUNT(*)
               FROM table_3 
              WHERE userid = $member_id 
             UNION ALL
             SELECT COUNT(*)
               FROM table_4 
              WHERE name = '$member_name' ) AS u" ;
     
    $result = mysql_query($query) or die();
     
    $count_row = mysql_fetch_assoc($result);
     
    echo $count_row['total'];

    that is the same as

    Code:
    $total_table_1_row + $total_table_2_row + $total_table_3_row + $total_table_4_row = ???
    but i need

    Code:
    $total_table_1_row = ?;
    $total_table_2_row = ?;
    $total_table_3_row = ?; 
    $total_table_4_row = ?;
    number of rows for each table.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    sorry but i not sure how UNION works
    http://dev.mysql.com/doc/refman/5.1/en/union.html
    Code MySQL:
    SELECT COUNT(*) AS subtotal
               FROM table_1
              WHERE userid = $member_id
                AND foo = 1
             UNION ALL
             SELECT COUNT(*)
               FROM table_2
              WHERE userid = $member_id
                AND foo = 1
             UNION ALL
             SELECT COUNT(*)
               FROM table_3
              WHERE userid = $member_id
             UNION ALL
             SELECT COUNT(*)
               FROM table_4
              WHERE name = '$member_name'

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    You can't calculate the number of rows for four different tables seperately in one query, at least not efficiently. You need to do a SELCT COUNT(*) on each table individually.
    If I'm not mistaken MySQL stores the number of rows in a table in the meta data of that table, so it doesn't even have to access the data in the table to come up with the answer. Meaning the query is really fast even for huuuge tables.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    ... MySQL stores the number of rows in a table in the meta data of that table, so it doesn't even have to access the data in the table to come up with the answer.
    this is true only for MyISAM tables -- this count is easily maintained because it does a table lock for every update or delete
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    guido, you need to stick the table name into each of your SELECTs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    220
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    this is true only for MyISAM tables -- this count is easily maintained because it does a table lock for every update or delete
    what exactly is 'table lock'??

    so COUNT(*) is just retrieving the meta data of a table

    PHP function mysql_num_rows() = 'retrieving all the rows in the tables, shipping them down the pipe to php, and counting them there'

    ??

  9. #9
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,097
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Suppose you have several marbles and suppose I want to know how many how you have.

    I could ask "Which marbles do you have?" (SELECT size,description FROM marbles) and you could say:

    "I have a small blue one, a medium red one, a medium transparent one with a yellow swirl in it, and a large white one with colored spickles on it"

    I could then recount what you said and determine how many you have (mysql_num_rows()).

    Or, to save you some effort (query processing time) and information (data transfer) I could have just asked "How many marbles do you have?" (SELECT COUNT(*) FROM marbles)
    In which case you simply would have said "4"

    As for the table lock, it is a lock that prevents any process from INSERTing or UPDATEing rows in a table while another process is INSERTing or UPDATEing a row. Because of this locking MySQL can keep track for MyISAM tables how many rows are in it and store it in the meta-data, which makes COUNT(*) fast for MyISAM tables.
    This doesn't hold for InnoDB tables.
    To know what kind of tables you're using do a
    Code:
    SHOW CREATE TABLE your_table_name;
    and look for ENGINE= at the end

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    guido, you need to stick the table name into each of your SELECTs
    Rudy, at 11pm you can't expect me to be brilliant...


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
  •