William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

MySQL: 'select count(*)' is much faster than 'select *'

If we want to get the number of table records, normally there are 2 ways to do so:

  1. select * from table
  2. select count(*) from table

Examples:
(1). use ‘select * from table’

$num_rows=array();
$queries = array('select * from a', 'select * from b', ...);
foreach ($queries as $sql) {
  $result = mysql_query($sql, $link);
  $num_rows[] = mysql_num_rows($result);
  mysql_free_result($result);
}
 // use $num_rows[0], $num_rows[1] to get the table number.

(2). use ‘select count(*) from table’

$num_rows=array();
$queries = array('select count(*) from a', 'select count(*) from b', ..);
 foreach ($queries as $sql) {
  $result = mysql_query($sql, $link);
  $num_rows[] = mysql_fetch_row($result);
  mysql_free_result($result);
 }
// $num_rows[0][0],$num_rows[1][0] instead of $num_rows[0], $num_rows[1]...

The above 2 are not the same, with very big difference.
I found ‘select count(*) from table’ (2) is much faster than ‘select * from table’ (1). For total 4 tables, which records from 1000 to 260000, the speed is 2.78S vs. 32ms. The (2) is 9 times quicker than (1).

The (1) fetch all records and use mysql_num_rows to sum up the total number, eats a lot of memory.
The (2) just get the total number of the table, no table data touched, so save huge memory.
And, (1) is to search table itself; while (2) is to search table index, so 2 is much quicker.

So, for a indexed table, it is a better choice to always use (2) ‘select count(*) from table’ to get the table number.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: