List:General Discussion« Previous MessageNext Message »
From:Jim Faucette Date:July 19 1999 4:27pm
Subject:Re: Count(*) definitely slower than select col and count num rows combo
View as plain text  
Steven Roussey wrote:
> 
> Arg!
> 
> OK, I switched to the second method below on the site itself. Now things are
> somewhat reversed. Count(*) seems somewhat faster, and both methods are
> creating pauses. Then it occurred to me that both methods are relying on the
> index only. So I'm now using a third method (Type 3, Type 1 and 2 are
> repeated further, below):
> 
> select b from tbl where a=# and c=0;
> count number of rows in result set.
> 
> I'm selecting a column that is not part of the index used in the where
> clause.
> 
> The results are interesting. With no background traffic, Type 1 is twice as
> fast as Type 2 which itself is twice as fast of Type 3. So on a benchmark
> level, I'm selecting a query that is four times slower than the optimal
> choice.
> 
> But when there is real traffic hitting the server at the same time, things
> change. But not as much on the average, so much as on the maximum query
> length:
> 
> Type 1: average is 0.15165980696678 with max of 9.3659509420395
> Type 2: average is 0.2846958220005 with max of 5.8125519752502
> Type 3: average is 0.2899955201149 with max of 0.071884989738464
> 

I can't help you with your main question, but there seems to a problem
with this data.

Either your decimal places are off or this data is unreliable. The max
query time can't be less than the average time.

  jim...
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