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
2799
May 22, 2017 09:32AM
Re: Select count very slow
974
May 22, 2017 11:24AM
704
May 22, 2017 11:38AM
580
May 23, 2017 10:43AM
599
May 23, 2017 11:10AM
514
May 23, 2017 02:24PM
515
May 23, 2017 04:05PM
582
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.