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

Databases
1

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



$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

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

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

:slight_smile:

3

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,


$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

$total_table_1_row + $total_table_2_row + $total_table_3_row + $total_table_4_row = ???

but i need


$total_table_1_row = ?;
$total_table_2_row = ?;
$total_table_3_row = ?; 
$total_table_4_row = ?;

number of rows for each table.

4

sorry but i not sure how UNION works

http://dev.mysql.com/doc/refman/5.1/en/union.html


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

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

this is true only for MyISAM tables – this count is easily maintained because it does a table lock for every update or delete

7

guido, you need to stick the table name into each of your SELECTs

8

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

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


SHOW CREATE TABLE your_table_name;

and look for ENGINE= at the end

10

Rudy, at 11pm you can’t expect me to be brilliant… :smiley: