Mysql count (*) takes a lot of time? Any better options?

Question

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

Performs output for more than 10 minutes. Can this be configured on server settings? Since I have to run this query every hour for reports, a query that takes 10 minutes is not possible for my business people ...

Are there any options for 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 () is slow for tables with a million rows. But you can use the hack to find out how many rows are in the table, if you are not 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 the "rows" column you can see the number of rows.

Answer 2

Try ::

 Select count(1) from table 

Answer 3

change mysql to the latest version, e.g. 5.5.25 inno db. - myisam db may be slow when updating and deleting

distribute mysql data and log disk. - disk load balancing is very important

check key_buffer_size, read_buffer_size - no request cache

change disk to SSD - speed up

check mysql logs for maximum connection, slow query and others - speed occurs when each log is cleared

Show ... your table definitions for viewing indexes.