MySQL queries still slow after deleting bunch of records

The error seems to indicate a problem with the primary key on the table mycompany.fact.

InnoDB: Page number (if stored to page already) 266102,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 994
InnoDB: Page may be an index page where index id is 0 4668
InnoDB: (index "PRIMARY" of table "mycompany"."Fact")

You can open the dump file in any text editor and remove the primary key constraint from the CREATE TABLE schema definition. Then once you've restored your database, you can always add the primary key back in. You would also need to deactivate Foreign Key Checks in your backup file.

-- at the beginning of the file
SET foreign_key_checks = 0;

-- at the end of the file
SET foreign_key_checks = 1;

This may get your database restored, but if there is a problem with the data in the column you're using as the primary key, then you may see other errors.

If that didn't work, I would also try

1. Backing up all the databases
2. Stopping MySQL
3. Delete the ibdata and iblog files
4. Starting MySQL again
5. Restore all my databases.

If there is a problem with your InnoDB files, this may be a way to solve it.

The problem could also be deeper and running a fsck on the filesystem that stores your InnoDB files may find corruption on the disk. Rebooting your computer is one way to easily cause an fsck on all partitions.

This looks oddly familiar.

I have seen this occur with one of my web hosting client's DB servers. There was a particular table that crashed mysqld every single time you accessed it, even with SHOW CREATE TABLE.

The problem stems from a corrupt data dictionary. There is really no way to correct it. You could attempt to alter the tablespace_id within the .ibd file but the headache stems from locating the tablespace_id list internal to ibdata1.

Even if you create a MyISAM table with the same name in the same database as the original InnoDB table, you cannot convert it to InnoDB because the tablespace_id is already associated with the table name. This, of course, is a corrupted state. It's like having a pidgeon hole in ibdata1 that you cannot patch up without some exploratory surgery.

You may have to mysqldump everything except the database that houses the corrupt table. You would then have to mysqldump every table in that database except the corrupt table. Remember, it is the data dictionary's view of the table that is screwed up, not necessarily the table's data.

The only sure way to clean everything up is to perform the mysqldumps as I just specified, shutdown mysql, rm -rf all DB folders except /var/lib/mytsql/mysql, delete ibdata1, delete ib_logfile0, delete ib_logfile1, startup mysql, reload all mysqldumps. See my StackOverflow post about cleaning up your InnoDB infrastructure.

Since you are not using innodb_file_per_table, any tables with this corrupt state of things within ibdata1 are lost as casualities of war. My condolences.

For future reference, click here to see an artistic conception of InnoDB and its Internals.