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

Steven> Arg!
Steven> OK, I switched to the second method below on the site itself. Now things are
Steven> somewhat reversed. Count(*) seems somewhat faster, and both methods are
Steven> creating pauses. Then it occurred to me that both methods are relying on the
Steven> index only. So I'm now using a third method (Type 3, Type 1 and 2 are
Steven> repeated further, below):

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

Steven> I'm selecting a column that is not part of the index used in the where
Steven> clause.

Steven> The results are interesting. With no background traffic, Type 1 is twice as
Steven> fast as Type 2 which itself is twice as fast of Type 3. So on a benchmark
Steven> level, I'm selecting a query that is four times slower than the optimal
Steven> choice.

Steven> But when there is real traffic hitting the server at the same time, things
Steven> change. But not as much on the average, so much as on the maximum query
Steven> length:

Steven> Type 1: average is 0.15165980696678 with max of 9.3659509420395
Steven> Type 2: average is 0.2846958220005 with max of 5.8125519752502
Steven> Type 3: average is 0.2899955201149 with max of 0.071884989738464

Steven> The worst part about this, is that while a Type 1 or 2 query runs really
Steven> slow (on occasion) ***it is locking out all other selects*** even though
Steven> there is no WRITE lock in the queue. I see this in 'mysqladmin -i 1
Steven> processlist status'. I suspect that all operations that use only the index
Steven> are affected. This phenomenon causes a cascade effect that really degrades
Steven> performance. And oddly, it seems to happen only when the result set is
Steven> between 1-3 rows. Test results are below. So now we are using Type 3 query,
Steven> and I'm looking at the processlist to determine if other index-only type
Steven> queries are locking things up.

Steven> Does anyone know the code well enough to point me to the location where a
Steven> select gets optimized to use index only and requests a lock when being
Steven> placed in the queue?

Steven> Thanks!

Steven> Steven Roussey
Steven> Network54.com

Hi!

Why do you think there is a WRITE lock.  MySQL will never issue a
WRITE lock for a SELECT !

Could you mail me some output from 'mysqladmin proc' that shows what
you mean!

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