List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:July 30 1999 6:22pm
Subject:RE: Count(*) make a WRITE lock?
View as plain text  
>>>>> "Steven" == Steven Roussey <sroussey@stripped>
> writes:

Steven> This is from the change log for MySQL:

Steven> Sun Jul 12 12:38:45 1998  Michael Widenius  <monty@tik>

Steven> * Index only optimization;  Some queries are now resolved using
Steven>   only indexes.  Until MySQL 4.0 this works only for number columns.

Steven>   SELECT key_part1,key_part2 FROM table WHERE key_part1=#
>>> SELECT COUNT(*) FROM table WHERE key_part1=# and key_part2=#<<
Steven>   SELECT key_part2 FROM table GROUP BY key_part1;
Steven>   SELECT * FROM table ORDER BY key_part2;

Steven> The >>example above<< is the same that I am using. It should only
> be using
Steven> the index, not even bothering with loading up the records.

Steven> Since it is a special case, I figured there by be some side effects
Steven> somewhere.

Steven> explain select count(*) from tbl where a=10333 and b=0;
Steven> +-------+------+---------------+--------+---------+---------+------+--------
Steven> -----+
Steven> | table | type | possible_keys | key    | key_len | ref     | rows | Extra
Steven> |
Steven> +-------+------+---------------+--------+---------+---------+------+--------
Steven> -----+
Steven> | tbl   | ref  | index1,main   | index1 |       8 | ???,??? |    1 | Using
Steven> index |
Steven> +-------+------+---------------+--------+---------+---------+------+--------
Steven> -----+

Steven> I'm having difficulty discovering what I am doing wrong. I now log all the
Steven> mysql statements that last over 10 seconds. Its amazing! I have a 'replace
Steven> into' for a two part unique index table with 8100 rows that every couple of
Steven> hours takes over two minutes to complete. !? Of course, that causes all the
Steven> selects in queue to take at least that long (this cascade can go on for 20
Steven> minutes), so at least that part makes sense. My only guess in that example
Steven> is that there is a 10k blob in there. Can that really be it?

Steven> Anyhow, the count(*) above is sending data for over a minute very often, and
Steven> holds back all the other selects. So we have decided it would be faster (at
Steven> least on MySQL) to actually select the rows (thus going to the table, using
Steven> additional disk and memory resources, etc) and count the size of the result
Steven> set. Ick.


Doing the actual select should NEVER be faster.
Are you sure that the count(*) really is taking over a minute CPU
time;  You should check that it hasn't been waiting for some
particular update before it started!

How many rows does the select actually return?

What does 'mysqlamin proc' say about the select when it's running?

Count(*) make a WRITE lock?Steven Roussey16 Jul
  • performance questionPaul Schwarzl16 Jul
    • Re: performance questionTõnu Samuel16 Jul
      • Re: performance questionPaul DuBois17 Jul
    • performance questionsinisa16 Jul
    • Re: performance questionTõnu Samuel17 Jul
      • Re: performance questionPaul DuBois17 Jul
        • Re: performance questionMichael Widenius31 Jul
  • Count(*) make a WRITE lock?sinisa16 Jul
    • RE: Count(*) make a WRITE lock?Steven Roussey18 Jul
      • RE: Count(*) make a WRITE lock?sinisa18 Jul
      • RE: Count(*) make a WRITE lock?Michael Widenius30 Jul