0

I have a table with 1394971 records, is it possible to optimize the count(*) performance?

mysql aDB -e 'select * from Table limit 1;'

0m0.034s

mysql aDB -e 'select count(*) from Table'

0m0.505s

Now, from mysql count performance and https://www.percona.com/blog/2006/12/01/count-for-innodb-tables/ :

So if you have query like SELECT COUNT(*) FROM USER It will be much faster for MyISAM (MEMORY and some others) tables because they would simply read number of rows in the table from stored value. Innodb will however need to perform full table scan or full index scan because it does not have such counter

I don't want to create an additional "counter" table.

Should I use another engine? Which one? I don't want to use MyISAM. Is this possible with other databases?

Tohmaxxx
  • 423
  • 2
  • 9
  • The accepted answer to this one should provide the improvement performance you are hoping to see. Btw, I'm not sure why you are comparing a single row fetch (with `LIMIT 1`) with your `COUNT(*)` - why do you feel these are comparable? – Martin Jan 23 '19 at 10:00
  • @Tohmaxxx did you tried " select count(primary_key) from table; " , any difference? – jithin giri Jan 23 '19 at 10:00
  • Instead of Count(*) use any Count(Id). – Kandy Jan 23 '19 at 10:00
  • 1
    InnoDB doesn't have a row counter, because it's a transactional engine. It is possible/necessary for different transactions to 'see' different row counts (and query results in general), and the exact row count can't be defined as a single value at a point in time. You can get an approximate count by caching it periodically, or from `information_schema.`TABLES`. – Vatev Jan 23 '19 at 10:25
  • select TABLE_ROWS from information_schema.TABLES where TABLE_NAME = 'myTable'; – Tohmaxxx Jan 23 '19 at 11:15

0 Answers0