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


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

is taking more 10 mins exec output. can tuned in server parameters? should run query every hr reports, query taking 10mins not feasible business folks...

any options keep in memory in oracle?

mysql> show variables '%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 '%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(*) idea_sub_renewal ; +----------+ | count(*) | +----------+ | 13633246 | +----------+ top - 17:40:19 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

with innodb count() works tables million rows. can use hack see how many rows in table, if aren't using - use explain.

mysql> explain select count(1) history; +----+-------------+---------+-------+---------------+-----------+---------+------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | | +----+-------------+---------+-------+---------------+-----------+---------+------+----------+-------------+ | 1 | simple | history | index | null | history_1 | 12 | null | 17227419 | using index | +----+-------------+---------+-------+---------------+-----------+---------+------+----------+-------------+ 1 row in set (0.01 sec)

in column 'rows' can see number of rows.


Comments

Post a Comment

Popular posts from this blog

javascript - backbone.js Collection.add() doesn't `construct` (`initialize`) an object -

<!doctype html public "-//w3c//dtd xhtml 1.0 strict//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-strict.dtd"> <html> <head> <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script> <script src="http://documentcloud.github.com/underscore/underscore-min.js"></script> <script src="http://documentcloud.github.com/backbone/backbone-min.js"></script> </head> <body> <button id="cmd_create_event" name="cmd_create_event" type="button">create new `event`</button> <script type="text/javascript"> var eventmodel = backbone.model.extend({ initialize : function() { console.log("`event` initialized. id: " + this.cid); this.bind("change:status", function() { console.log(this.get("status") + " value `status`"); }); this.bind("error", function(model, e...
Read more

php - Get uncommon values from two or more arrays -

is there function in php give array of uncommon values 2 or more arrays? for example: $array1 = array( "green", "red", "blue"); $array2 = array( "green", "yellow", "red"); .... $result = function_needed($array1, $array2,...); print_r($result); should give output: array("blue", "yellow", ...); use array_diff , array_merge : $result = array_merge(array_diff($array1, $array2), array_diff($array2, $array1)); here's demo. for multiple arrays, combine callback , array_reduce : function unique(&$a, $b) { return $a ? array_merge(array_diff($a, $b), array_diff($b, $a)) : $b; } $arrays = array( array('green', 'red', 'blue'), array('green', 'yellow', 'red') ); $result = array_reduce($arrays, 'unique'); and here's demo of that.
Read more

Adding duplicate array rows in Php -

i create function in php adds rows of array shared column value. so input. $test = array( array("c", 5, 6), array("c", 2, 3), array("test", 5, 6) ); and output. $testduplicatefree = array( array("c", 7, 9), array("test", 5, 6) ) ); i'm thinking function combine_duplicates($array,$col){ ... ... return $duplicatefreearray; } where $col duplicate free array. so, in case, combine_duplicates($test,0); would me desired output. this. <?php function combine_duplicates($array,$col) { $index = array(); foreach ($array $row) { $key = $row[$col]; if (!isset($index[$key])) { $index[$key] = $row; } else { ($i = 0; $i < count($row); ++$i) { if ($i != $col) { $index[$key][$i] += $row[$i]; } } } } return array_values($index); } $array = array( array("c", 5, 6), array("c", 2, 3), array("test", 5, 6) ); print_r(combine_duplicates($array, 0)); try here: http://codepad.org/mdhesqhi
Read more