List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:July 30 1999 10:00pm
Subject:Count(*) definitely slower than select col and count num rows combo
View as plain text  
>>>>> "Steven" == Steven Roussey <sroussey@stripped>
> writes:

Steven> I doubt this will show up on a benchmark, since I think real world access
Steven> creates an environment where this bug occurs. The numbers below are an
Steven> average from a 100 iteration test program:

Steven> select count(*) from tbl where a=# and c=0;

Steven> 0.16111115813255
Steven> 0.073595218658447
Steven> 0.10604506850243

Steven> The test seems to go quick but pauses from 1 to 5 times for several seconds
Steven> (that is, a query takes several seconds). This is what kills the average.

Steven> select a from tbl where a=# and c=0;
Steven> count number of rows in result set.

Steven> 0.0011586213111877
Steven> 0.0015420305728912
Steven> 0.0011256754398346

Steven> Again, tbl has a unique index(a,b) and a non-unique index(a,c). The number
Steven> of rows we are getting always falls between 0 and 1000.

Steven> I've run this many times. Sometimes both will take much longer (something
Steven> else on the server is causing the pause), but statistically the numbers
Steven> above consistant and very repeatable.

Steven> A factor of 100x difference! We are immediately converting to the second
Steven> format.

Steven> Steven Roussey
Steven> Network54.com

Hi!

Could you mail me a copy of the test program ?
select count(*) should be much faster!

Regards,
Monty
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