title: How to speed up InnoDB count(*) query?
body: <p>There are a number of similar questions on here, but a lot of the answers say to force the use of an index and that doesn't seem to speed anything up for me.</p>
  
  <p>I am wanting to show a "live" counter on my website showing the number of rows in a table. Kind of like how some websites show the number of registered users, or some other statistic, in "real time" (i.e. updated frequently using ajax or websockets).</p>
  
  <p>My table has about 5M rows. It's growing fairly quickly and there is a high volume of inserts and deletes on it. Running</p>
  
  <pre><code>select count(*) from my_table
  </code></pre>
  
  <p>Takes 1.367 seconds, which is unacceptable because I need my application to get the new row count about once per second. </p>
  
  <p>I tried what many of the answers on here suggest and changed the query to:</p>
  
  <pre><code>select count(*) from my_table use index(my_index)
  </code></pre>
  
  <p>Where <code>my_index</code> is <code>Normal</code>, <code>BTREE</code> on a <code>bigint</code> field. But the time actually increased to 1.414 seconds.</p>
  
  <p>Why doesn't using an index speed up the query as many answers on here said it would?</p>
  
  <p>Another option some answers suggest is to put a trigger on the table that increments a column in another table. So I could create a <code>stats</code> table and whenever a row is inserted or deleted in <code>my_table</code> have a trigger increment or decrement a column in the <code>stats</code> table. Is this the only other option, since using an index doesn't seem to work?</p>
  
  <p><strong>EDIT:</strong> Here's a perfect example of the type of thing I'm trying to accomplish: <a href="https://www.freelancer.com" rel="nofollow noreferrer">https://www.freelancer.com</a>. Scroll to the bottom of the page and you'll see:</p>
  
  <p><img src="https://i.stack.imgur.com/vGkqR.png" alt="enter image description here"></p>
  
  <p>Those numbers update every second or so.</p>