Monday, July 24, 2006

Where did the records go?

I experienced a strange thing happening on one of the tables I converted recently to InnoDB from MyISAM. When the table was using MyISAM, MySQL was reporting 47219404 rows in the output of

SHOW TABLE STATUS LIKE 'book_v2' \G


After converting the table to InnoDB and loading data using

INSERT INTO ... SELECT * FROM ...

I noticed that

SHOW TABLE STATUS LIKE 'book_v2_innodb' \G

reports 19265701 records and for the same table running

SELECT count(identifier) FROM db.book_v2_innodb;

returns

+-------------------+
| count(identifier) |
+-------------------+
| 19996851 |
+-------------------+
1 row in set (6 min 31.14 sec)


Now where did the remaining records go and why is MySQL reporting different value for count(identifier) than the number of rows in "SHOW TABLE STATUS" output?

One thing I would like to note is that the records were inserted after setting UNIQUE_CHECK and AUTOCOMMIT to 0.

<update</>
Another weird thing is (notice the number of rows):

mysql> show table status like 'guestbook_v2' \G
*************************** 1. row ***************************
Name: guestbook_v2
Engine: InnoDB
Version: 9
Row_format: Dynamic
Rows: 19265701
Avg_row_length: 305
Data_length: 5895094272
Max_data_length: NULL
Index_length: 1141899264
Data_free: 0
Auto_increment: 53756907
Create_time: 2006-07-24 20:40:23
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=1000000000 avg_row_length=256
Comment: InnoDB free: 380928 kB
1 row in set (2.60 sec)


mysql> show table status like 'guestbook_v2' \G
*************************** 1. row ***************************
Name: guestbook_v2
Engine: InnoDB
Version: 9
Row_format: Dynamic
Rows: 19445335
Avg_row_length: 303
Data_length: 5895094272
Max_data_length: NULL
Index_length: 1141899264
Data_free: 0
Auto_increment: 53756907
Create_time: 2006-07-24 20:40:23
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=1000000000 avg_row_length=256
Comment: InnoDB free: 380928 kB
1 row in set (1.02 sec)


mysql> show table status like 'guestbook_v2' \G
*************************** 1. row ***************************
Name: guestbook_v2
Engine: InnoDB
Version: 9
Row_format: Dynamic
Rows: 18951343
Avg_row_length: 311
Data_length: 5895094272
Max_data_length: NULL
Index_length: 1141899264
Data_free: 0
Auto_increment: 53756907
Create_time: 2006-07-24 20:40:23
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=1000000000 avg_row_length=256
Comment: InnoDB free: 380928 kB
1 row in set (0.79 sec)


mysql> show table status like 'guestbook_v2' \G
*************************** 1. row ***************************
Name: guestbook_v2
Engine: InnoDB
Version: 9
Row_format: Dynamic
Rows: 18996251
Avg_row_length: 310
Data_length: 5895094272
Max_data_length: NULL
Index_length: 1141899264
Data_free: 0
Auto_increment: 53756907
Create_time: 2006-07-24 20:40:23
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: max_rows=1000000000 avg_row_length=256
Comment: InnoDB free: 380928 kB
1 row in set (1.65 sec)


Weird, huh?

5 comments:

Peter Zaitsev said...

Frank,

"Rows" in SHOW TABLE STATUS is estimtion only for Innodb tables which is done using random dives to btree each time it is requested, so it can fluctuate a lot over time.

Xaprb said...

InnoDB doesn't keep a row count in the table itself, as MyISAM does. This is because of transactional concurrency requirements. Therefore, count(*) works differently; in MyISAM, it can just ask the table how many rows it has, but in InnoDB, it has to scan some index and find out. This is slow. For that reason, the SHOW TABLE STATUS value is only an estimate, and can be very wrong, in InnoDB.

gloomy said...

Hi, check the manual (the "rows" section): http://dev.mysql.com/doc/refman/4.1/en/show-table-status.html

Frankly Speaking! said...

Thanks Peter.

But that still doesn't explain why running COUNT(*) on MyISAM returns 47000000 and running COUNT(*) on InnoDB (after conversion) returns 19000000 records. Any ideas?

Scott Noyes said...

And

SELECT COUNT(*) FROM
theMyISAMTable
LEFT JOIN theInnoDBTable USING (thePrimaryKey)
WHERE theInnoDBTable.thePrimaryKey IS NULL;

returns?