Dont delete the files. Those are the files where the actual data, indexes and the innodb log are stored for all innodb databases

If we insert records in the db, will the size of these files increase?.

Will these files affects the execution speed.

If databases r deleted will the size of these files decrease?

Quote:

If we insert records in the db, will the size of these files increase?.

Yes when the ibdata1 file is to small for the amount of data that you are trying store in it. It will grow automatically.
The ib_logfile files will continue to be the same size always, they do not grow.

Quote:

Will these files affects the execution speed.

The question isn't if the files will affect the execution speed.

The quesiton is if more _data_ in the database will affect execution speed, and the answer is, yes more data in the database will affect execution speed.
But if you have proper indexes in your tables you will not notice it that much.

Quote:

If databases r deleted will the size of these files decrease?

No, unfortunately not.
But generally when you have a database you seldom have the situation that you need to shrink the table space.
Because often the size of a database is either growing or pretty stable in size.

sterin wrote on Mon, 18 June 2007 03:23

No, unfortunately not. But generally when you have a database you seldom have the situation that you need to shrink the table space. Because often the size of a database is either growing or pretty stable in size.

I once had to drop a 2 GB table, but the ibdata file did not reflect this. There is no way to reclaim this empty space?

A very cumbersum way:
1.
mysqldump all databases

2.
Remove the ibdata1 file.

3.
Start the mysqld process.
It will now recreate the ibdata1 table space since it recognizes that it is missing.

4.
Import the sql dump from step 1 and you are back again.


But as you see it is not a fun operation so unless you need the 2GB space I wouldn't recommend it.

If you're using the innodb_file_per_table option, then each InnoDB table gets its own .ibd file, so you can recover freed-up space in a table by rebuilding just that one table. The easiest way to rebuild an InnoDB table is to run the command, "ALTER TABLE tablename ENGINE=InnoDB;". However, this will build the new copy of the table on disk before dropping the old one, so you'll need to have enough free disk space to keep the entire second copy of the table. If you're tight on disk space on the database server (which is probably why you want to free up the space trapped in the InnoDB tablespace in the first place), then you can mysqldump the table to another machine with more free space and then re-import it back into the database server. It's still a pain to have to do that, but it's a lot better than having to dump ALL of your tables.