mysql count(*) takes long time? Any better options?

Question

mysql> select count(*) from id_renewal ;
+----------+
| count(*) |
+----------+
| 13633246 |
+----------+

Is taking more than 10 mins to exec the output. Can this be tuned in server parameters? As I should run this query every hr for reports, query taking 10mins is not feasible for my business folks...

Any options to KEEP in memory like in oracle?

mysql> show variables like '%cache%';
+------------------------------+----------------------+
| Variable_name                | Value                |
+------------------------------+----------------------+
| binlog_cache_size            | 32768                |
| have_query_cache             | YES                  |
| key_cache_age_threshold      | 300                  |
| key_cache_block_size         | 1024                 |
| key_cache_division_limit     | 100                  |
| max_binlog_cache_size        | 18446744073709547520 |
| query_cache_limit            | 1048576              |
| query_cache_min_res_unit     | 4096                 |
| query_cache_size             | 134217728            |
| query_cache_type             | ON                   |
| query_cache_wlock_invalidate | OFF                  |
| table_definition_cache       | 512                  |
| table_open_cache             | 2048                 |
| thread_cache_size            | 16                   |
+------------------------------+----------------------+
14 rows in set (0.00 sec)

mysql> show global status like '%Qc%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 118       |
| Qcache_free_memory      | 133367960 |
| Qcache_hits             | 71077421  |
| Qcache_inserts          | 137390744 |
| Qcache_lowmem_prunes    | 18066     |
| Qcache_not_cached       | 120209332 |
| Qcache_queries_in_cache | 427       |
| Qcache_total_blocks     | 990       |
+-------------------------+-----------+



mysql> select count(*) from idea_sub_renewal ;
+----------+
| count(*) |
+----------+
| 13633246 |
+----------+


top - 17:40:19 up 148 days, 17:51, 10 users,  load average: 0.83, 0.91, 1.00
Tasks: 257 total,   1 running, 251 sleeping,   0 stopped,   5 zombie
Cpu(s):  2.0%us,  0.6%sy,  0.0%ni, 97.1%id,  0.0%wa,  0.0%hi,  0.3%si,  0.0%st
Mem:   8167348k total,  8124120k used,    43228k free,    33928k buffers
Swap: 16386260k total,   709864k used, 15676396k free,  4615456k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                          
26329 sym       19   0 1195m  64m 7456 S 13.3  0.8  66:34.40 java                                                                                                                              
12079 mysql     15   0 1725m 463m 4840 S  6.3  5.8  24227:57 mysqld                                                                                                                            
  477 sym       18   0  674m  62m 7260 S  1.0  0.8   2:25.59 java                                                                                                                              
26948 powerdev  16   0 12896 1232  824 S  0.7  0.0   0:07.90 top                                                                                                                               
18843 sym       19   0 1271m 494m 7364 S  0.3  6.2  10:19.89 java                                                                                                                              
26379 sym       21   0 1203m 299m 7464 S  0.3  3.8   1:36.90 java                                                                                                                              
29872 sym       18   0 1238m 869m 7816 S  0.3 10.9   7:42.33 java    

Answer 1

With InnoDB COUNT() works slowly for tables with million rows. But you can use a hack to see how many rows in table, if you aren't using WHERE - use EXPLAIN.

mysql> explain select count(1) from history;
+----+-------------+---------+-------+---------------+-----------+---------+------+----------+-------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref  | rows     | Extra       |
+----+-------------+---------+-------+---------------+-----------+---------+------+----------+-------------+
|  1 | SIMPLE      | history | index | NULL          | history_1 | 12      | NULL | 17227419 | Using index |
+----+-------------+---------+-------+---------------+-----------+---------+------+----------+-------------+
1 row in set (0.01 sec)

In column 'rows' you can see the number of rows.

Answer 2

try select (distinct id) from tbl , id beeing the primary key field. So mysql (8) is using the index, without distinct the same query takes 16 seconds for a 100000 records instead of 0.096 seconds!