Tuesday, July 25, 2006

MyISAM to InnoDB: Lost Rows 2

Thank you for the replies to my previous post.

However, let me rephrase the question:

I have a table of type MyISAM that is reporting 47 million rows when I do a SELECT COUNT(*). When I convert this table to InnoDB, running a SELECT COUNT(*) returns only 19 million rows. The conversion confirms 19 million rows were inserted and reports no warnings or duplicates.

I have done the conversion to InnoDB using the following ways
1. by dumping all the data in a text file and loading it.
2. by using ALTER TABLE

Why is the record count so low after conversion to InnoDB?
Who should I believe: InnoDB or MyISAM?
Any ideas as to what can be done to avoid loss of this many rows?

I will be posting output from my latest conversion attempt in some time.

PS: Thank you for all the replies. It turned out that the index was corrupt and after running OPTIMIZE table and then converting to InnoDB everything went fine, i.e. all the records were converted to InnoDB.

1 comment:

Joshua said...

You've probably already checked this, but are you indexes corrupt on the MyISAM tables? That's the only thing I can think of that would generate "off" row counts.