List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:February 21 2007 6:14pm
Subject:Re: row count inconsistency
View as plain text  
At 10:34 AM -0600 2/21/07, Gerald L. Clark wrote:
>Marty Landman wrote:
>>The table was created and then loaded and not modified in any way I'm aware
>>of afterwards. It's on a local, only accessible by me server.
>>Really weird thing about it is that I wrote/ran a program specifically to
>>find any gaps in the id sequence - because of the size of the table it took
>>days to run but the result was
>>IOW it confirms the max id that Mysql gave, but also indicates that there
>>are no gaps in the row id's all the way through. This doesn't make sense to
>>me in light of Mysql reporting the count as posted previously i.e.
>>>mysql> select count(*) from fidcid;
>>>| count(*)  |
>>>| 100480507 |
>>>1 row in set (0.09 sec)
>If this table is InnoDB, then count(*) is
>just an approximation.
>Gerald L. Clark
>Supplier Systems Corporation

select count(*), as well as other functions like max(), min() etc 
should be accurate regardless of table type; it's the 'show table 
status' report that may be inaccurate for Innodb:

Going back to the original problem:

What is the table type & MySQL version? Also, if you drop the 
auto_increment column and recreate it (on a copy of the original 
table, if necessary), are these results repeatable?

Also, if the server has been shutdown improperly, there may be table 

MyISAM tables:

InnoDB problems:


PS. This may be an obvious question, but: are you sure data loading 
was finished before running the select count(*) and select max(id) 

+--------------- my people are the people of the dessert, ---------------+
| Steve Edberg                       |
| UC Davis Genome Center                            sbedberg@stripped |
| Bioinformatics programming/database/sysadmin             (530)754-9127 |
+---------------- said t e lawrence, picking up his fork ----------------+
row count inconsistencyMarty Landman20 Feb
  • Re: row count inconsistencySteve Edberg20 Feb
Re: row count inconsistencyMarty Landman21 Feb
  • Re: row count inconsistencyGerald L. Clark21 Feb
    • Re: row count inconsistencySteve Edberg21 Feb