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
143
May 22, 2017 09:32AM
Re: Select count very slow
73
May 22, 2017 11:24AM
63
May 22, 2017 11:38AM
67
May 23, 2017 10:43AM
109
May 23, 2017 11:10AM
57
May 23, 2017 02:24PM
70
May 23, 2017 04:05PM
67
May 23, 2017 08:04PM


Sorry, only registered users may post in this forum.

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.