Re: row count inconsistency

[Steve Edberg]

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
>>
>>1-100537311
>>
>>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:

http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html

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 
corruption:

MyISAM tables:
http://dev.mysql.com/doc/refman/5.0/en/myisam-table-problems.html

InnoDB problems:
http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html

	steve

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


-- 
+--------------- my people are the people of the dessert, ---------------+
| Steve Edberg                                http://pgfsun.ucdavis.edu/ |
| UC Davis Genome Center                            sbedberg@stripped |
| Bioinformatics programming/database/sysadmin             [phone] |
+---------------- said t e lawrence, picking up his fork ----------------+