MySQL Forums
Forum List  »  InnoDB

Re: count(*) speed in InnoDB
Posted by: Rick James
Date: February 20, 2011 10:08PM

1. Restart the server (mysqld).
2. Run the SELECT COUNT(*) -- it takes a rather long amount of time, correct?
3. Run it again -- now it takes 0.00 seconds, correct?
4. Run it again, but with an extra spacing in the SELECT -- slow again, correct?
5. SHOW VARIABLES LIKE 'query_cache%'; -- type = ON, correct?

Read about the "Query cache". Then abandon your extra table. The QC can do the job for you, and do it correctly every time. However, it will be slow after any changes to the table.

While you munch on that, I'll explain why your extra table takes slightly longer. The SELECT from that table happens right after you stored the new count. That means that the QC needs to be refreshed for that table. So, it cannot use the QC, and must actually fetch the value.

SHOW VARIABLES LIKE 'innodb%';
How much RAM do you have?
A typical cause of sluggishness in InnoDB is lack of tuning the buffer_pool_size. See
http://mysql.rjweb.org/doc.php/memory

Options: ReplyQuote


Subject
Views
Written By
Posted
4748
February 19, 2011 05:13AM
Re: count(*) speed in InnoDB
1901
February 20, 2011 10:08PM
1535
February 26, 2011 08:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.