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

>> Sinisa wrote:
>> 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.

<cut>

Steven> mysqladmin processlist shows the command 'count(*) from tbl where a=# and
Steven> c=0' as sending data, while all the other threads fill up with selects on
Steven> that same table. All these other selects are Locked. No
Steven> inserts/updates/deletes in the queue. If I change the queries such that no
Steven> query relies solely on the index, then mysql will process and send data for
Steven> all the selects at the same time. This is more important than the fact that
Steven> the query that locks everything up is taking 5-10 seconds.

Steven> I will try and write a multithreaded test program and send the results on
Steven> Tuesday.

What do you mean with 'locked'.

Steven> On a side note, changing to the Query Type 3 throughout our server has had
Steven> an amazing impact. It should be somewhat slower, but instead it is much
Steven> faster. Before, we would limit apache to 100 or so processes, and it was
Steven> never enough. They would all be full, the site slow. Today, even with higher
Steven> traffic, we have only an average of about 22 apache servers in use. The
Steven> total number of processes for the server fell drastically and the server
Steven> responds much much faster. I'd suggest other people watch their processlist
Steven> for strange behavior. If nothing else, you get to see what the server really
Steven> is doing. Its fun!

Steven> Thanks for all your help!

Could you also test this query:

select count(b) from tbl where a=# and c=0;

The problem could be that the temporary locks on the key cache (when
using index scanning) somehow doesn't give the other threads enough
CPU.

Recoding the key cache to use reader/write locks could solve this.

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