List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:July 19 1999 4:54pm
Subject:Re: Count(*) definitely slower than select col and count num rows combo
View as plain text  
sinisa@stripped wrote:
> 
> Steven Roussey writes:
>  > Hi! Thanks for helping try and solve this thing. The numbers I printed were
>  > from the PHP test program below. Also, in our application, the count will
>  > always be between 0 and 1000 and the result set should only need come from
>  > an index file.
>  >
>  > Also, we are using Linux 2.2.10smp, Apache 1.3.6, PHP 3.0.11, and MySQL
>  > 2.22.25.
>  >
>  > Thanks!
>  >
>  > PS The test below only gives the strange results when there is background
>  > traffic that uses the count(*) method. When the background traffic uses the
>  > other method, this test will give different results (similar to when there
>  > is no background traffic).
>  >
>  > PPS Another odd thing. I watch the test print out the results below, and I
>  > noticed it pause strangely (when doing it either way) just before it printed
>  > a result count that is betwen one and three. It doesn't pause every time
>  > there is a count of one, two, or three, but every time it pauses the next
>  > result count is in that range (and it pauses in the same spots). The pause
>  > is long; where the time to do the query is usually much below 1 second, the
>  > paused ones are over 4-5 seconds. Don't know if this helps, but I thought
>  > I'd throw it in. And yes, I did a full isam check with recovery before doing
>  > these tests. Also, we have lots of disk space, the file system is OK, etc. I
>  > think the mysql buffers are OK, but since the pauses are on a result set or
>  > count whcih is small, I don't think this is a problem. The table itself,
>  > however, is 1-2 million records.
>  >
>  >
>  > <%
>  > function getmicrotime(){
>  >      $mtime = microtime();
>  >      $mtime = explode(" ",$mtime);
>  >      $mtime = $mtime[1] + $mtime[0];
>  >      return ($mtime);
>  > }
>  >
>  > $num =100;
>  >
>  > for ($i=1;$i<=$num;$i++) {
>  >
>  >      $j = 20333 + $i;
>  >
>  >      $mtime1 = getmicrotime();
>  >
>  > //  uncomment for test 1
>  > //   $res = mysql_query("select count(*) from tbl where a=$j and c=0");
>  > //   list($k) = mysql_fetch_row($res);
>  >
>  > //  uncomment for test 2
>  > //   $res = mysql_query("select forumid from tbl where a=$j and c=0");
>  > //   $k = mysql_num_rows($res);
>  >
>  >      $mtime2 = getmicrotime();
>  >      if ($etime > 0.5)
>  >              $etime = "(" . number_format($etime,4) . ")";
>  >      else
>  >              $etime = "";
>  >
>  >      echo("$i:$k$etime ");flush();
>  > }
>  > echo("<P>" . ($ttl/$num) . "<P>");
>  > %>
>  >
>  > Steven Roussey
>  > Network54.com
>  >
>  > > -----Original Message-----
>  > > From: sinisa@stripped [mailto:sinisa@stripped]
>  > > Sent: Sunday, July 18, 1999 5:16 AM
>  > > To: sroussey@stripped
>  > > Cc: mysql@stripped
>  > > Subject: Count(*) definitely slower than select col and count num rows
>  > > combo
>  > >
>  > >
>  > > Steven Roussey writes:
>  > >  > I doubt this will show up on a benchmark, since I think real
>  > > world access
>  > >  > creates an environment where this bug occurs. The numbers below are
> an
>  > >  > average from a 100 iteration test program:
>  > >  >
>  > >  > select count(*) from tbl where a=# and c=0;
>  > >  >
>  > >  > 0.16111115813255
>  > >  > 0.073595218658447
>  > >  > 0.10604506850243
>  > >  >
>  > >  > The test seems to go quick but pauses from 1 to 5 times for
>  > > several seconds
>  > >  > (that is, a query takes several seconds). This is what kills
>  > > the average.
>  > >  >
>  > >  > select a from tbl where a=# and c=0;
>  > >  > count number of rows in result set.
>  > >  >
>  > >  > 0.0011586213111877
>  > >  > 0.0015420305728912
>  > >  > 0.0011256754398346
>  > >  >
>  > >  > Again, tbl has a unique index(a,b) and a non-unique
>  > > index(a,c). The number
>  > >  > of rows we are getting always falls between 0 and 1000.
>  > >  >
>  > >  > I've run this many times. Sometimes both will take much longer
>  > > (something
>  > >  > else on the server is causing the pause), but statistically the
> numbers
>  > >  > above consistant and very repeatable.
>  > >  >
>  > >  > A factor of 100x difference! We are immediately converting to
>  > > the second
>  > >  > format.
>  > >  >
>  > >  > Steven Roussey
>  > >  > Network54.com
>  > >  >
>  > >
>  > > Hi Steven !
>  > >
>  > >
>  > > First of all, would you explain what are those numbers.
>  > >
>  > > Second, what is '#'. If you change '#' and get various speed results,
>  > > that is OK, because query speed does depend on the number of rows
>  > > satisfying some conditions.
>  > >
>  > >
>  > > Sinisa
>  > >
>  > > +----------------------------------------------------------------------+
>  > > | TcX  ____  __     _____   _____  ___     ==  mysql@stripped            |
>  > > |     /*/\*\/\*\   /*/ \*\ /*/ \*\ |*|     Sinisa Milivojevic          |
>  > > |    /*/ /*/ /*/   \*\_   |*|   |*||*|     mailto:sinisa@stripped|
>  > > |   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*|     Larnaka, Cyprus             |
>  > > |  /*/     /*/  /*/\*\_/*/ \*\_/*/ |*|____                             |
>  > > |  ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^                            |
>  > > |             /*/             \*\                Developers Team       |
>  > > +----------------------------------------------------------------------+
>  > >
>  >
>  >
> 
> Sorry, but I truly do not have time to analyze your program. Plus I am
> as versed in PHP as in African poetry, no pun intended.
> 
> So, I repeat my questions:
> 
> First of all, would you explain what are those numbers.
> 
> Second, what is '#'. If you change '#' and get various speed results,
> that is OK, because query speed does depend on the number of rows
> satisfying some conditions.
> 
> Sinisa
> 

Sinisa:

I think you know Perl better than African poetry :-), and PHP is close
enough to Perl to be able to read it if you know Perl. Kind of like for
me to understand Ukrainian, or for you to understand Russian. Anyway, it
seems like those mysterious numbers are supposed to be the query time
per record in microseconds. 

Steve:

Can you do those benchmarks from either command line client, or a
non-webserver program, and make sure there is no other major activity in
the database or on the machine where you are benchmarking? What you are
reporting seems to indicate that your benchmark is not the only thing
that occupies your server.

-- 
Sasha Pachev
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)
Thread
Count(*) definitely slower than select col and count num rows comboSteven Roussey18 Jul
  • Count(*) definitely slower than select col and count num rows combosinisa18 Jul
    • RE: Count(*) definitely slower than select col and count num rows comboSteven Roussey18 Jul
      • RE: Count(*) definitely slower than select col and count num rows combosinisa19 Jul
    • Re: Count(*) definitely slower than select col and count num rows comboSasha Pachev19 Jul
      • RE: Count(*) definitely slower than select col and count num rows comboSteven Roussey19 Jul
        • RE: Count(*) definitely slower than select col and count num rows comboMichael Widenius31 Jul
      • Re: Count(*) definitely slower than select col and count num rows combosinisa20 Jul
  • RE: Count(*) definitely slower than select col and count num rows comboSteven Roussey19 Jul
    • Re: Count(*) definitely slower than select col and count num rows comboJim Faucette19 Jul
      • RE: Count(*) definitely slower than select col and count num rows comboSteven Roussey19 Jul
        • RE: Count(*) definitely slower than select col and count num rows comboMichael Widenius31 Jul
    • RE: Count(*) definitely slower than select col and count num rows comboMichael Widenius31 Jul
  • Count(*) definitely slower than select col and count num rows comboMichael Widenius31 Jul