MySQL Forums
Forum List  »  InnoDB

Re: Select count very slow
Posted by: Peter Brawley
Date: May 22, 2017 11:24AM

In a transactional table, count(*) entails actual row counting, so in big tables it's necessarily slow.

If an estimate is good enough, pick up the row count value from the 5th row of SHOW TABLE STATUS FROM yourdb WHERE name=yourtable.

If an exact count is needed quickly, you can create your own stats table and write insert & delete triggers to update it. Beware that Drop/Create and Truncate will require that you zero your stored rowcount.

Your 2nd instance goes with default InnoDB settings. Did you test count(*) with default settings on the first server?

Options: ReplyQuote


Subject
Views
Written By
Posted
367
May 22, 2017 09:32AM
Re: Select count very slow
207
May 22, 2017 11:24AM
169
May 22, 2017 11:38AM
152
May 23, 2017 10:43AM
193
May 23, 2017 11:10AM
138
May 23, 2017 02:24PM
159
May 23, 2017 04:05PM
134
May 23, 2017 08:04PM


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.