Re: Select count very slow
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?
Subject
Views
Written By
Posted
6286
May 22, 2017 09:32AM
Re: Select count very slow
1927
May 22, 2017 11:24AM
1411
May 22, 2017 11:38AM
1180
May 23, 2017 10:43AM
1188
May 23, 2017 11:10AM
1171
May 23, 2017 02:24PM
1243
May 23, 2017 04:05PM
1375
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.