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
6290
May 22, 2017 09:32AM
Re: Select count very slow
1929
May 22, 2017 11:24AM
1413
May 22, 2017 11:38AM
1181
May 23, 2017 10:43AM
1190
May 23, 2017 11:10AM
1175
May 23, 2017 02:24PM
1249
May 23, 2017 04:05PM
1377
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.