Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

It's a known issue that innodb is slow in

SELECT count(*) FROM Table

Hence I avoided that. But I notice that whenever I login to phpmyadmin, it will automatically execute a count(*) for my innodb table with 19k rows, which can take up to a minute to execute. Yet it doesn't initiate such count(*) for the other innodb table with 4 million rows.

Another clue, in phpmyadmin, under the Records column, the number of records for the 19k-row table is showing the exact value whereas the 4-million-row table is showing an approx.

I understand innodb table gives an estimation of the row count. But it seems that for smaller table, in this case with 19k rows, phpmyadmin decides to do a count(*) instead of just giving an estimation.

Question is, is there a way to stop phpmyadmin from doing such count on my innodb table? It's okay to just give an estimation instead of doing an expensive row count whenever I refresh my phpmyadmin screen.

share|improve this question

migrated from stackoverflow.com Mar 1 '11 at 12:12

This question came from our site for professional and enthusiast programmers.

    
Think I found the answer. There is a $cfg['MaxExactCount'] = 20000; in phpmyadmin settings. Only rows more than 20k will give an estimates. My next question is, where do I edit $cfg['MaxExactCount']? –  Kenny Feb 28 '11 at 1:40
    
@Kenny, Simply overwrite the value 20000 with a smaller number, e.g. 300, save the file, refresh phpmyadmin and you're ready to go. –  Pacerier Apr 9 at 15:05

2 Answers 2

Edit config.inc.php, and change the setting for MaxExactCount.

$cfg['MaxExactCount']
    Type:   integer
    Default value:  500000

For InnoDB tables, determines for how large tables phpMyAdmin should get the 
exact row count using SELECT COUNT. If the approximate row count as returned 
by SHOW TABLE STATUS is smaller than this value, SELECT COUNT will be used, 
otherwise the approximate count will be used.
share|improve this answer
    
Link down............ –  Pacerier Apr 9 at 15:02
    
@Pacerier: Better link. –  Mike Sherrill 'Cat Recall' Apr 9 at 16:30
1  
Ah, now you made my answer redundant....... –  Pacerier Apr 11 at 16:52

phpMyAdmin will only do an actual select count(*) if the estimated number of rows are within the set threshold (default is 500k depending on your version):

$cfg['MaxExactCount']

For InnoDB tables, determines for how large tables phpMyAdmin should get the exact row count using SELECT COUNT. If the approximate row count as returned by SHOW TABLE STATUS is smaller than this value, SELECT COUNT will be used, otherwise the approximate count will be used.

Simply set the threshold to a smaller number to avoid pointless select count(*)s.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.