2

Was reading the mysql engine innoDB tutorial, I came across this sentence

"InnoDB stores its tables and indexes in a tablespace"

Can any body explain me this what does it means.

Thank you :)

0

3 Answers 3

3

A tablespace is essentially a black box that InnoDB manages allby itself

tablespace which is essentially a virtual filesystem spanning one or more files on disk.InnoDB uses tablespace for many purpose It keeps its undo log,insert buffer,doublewrite buffer,and other internal structure in the table space

By default, all InnoDB tables and indexes are stored in the system tablespace. As an alternative, you can store each InnoDB table and its indexes in its own file. This feature is called “multiple tablespaces” because each table that is created when this setting is in effect has its own tablespace.

Advantages of Per-Table Tablespaces

You can reclaim disk space when truncating or dropping a table. For tables created when file-per-table mode is turned off, truncating or dropping them creates free space internally in the ibdata files. That free space can only be used for new InnoDB data.

InnoDB File-Per-Table Mode

The InnoDB tablespace

1

By default, the InnoDB engine shares files for multiple tables. If Country is an InnoDB table, there will be a Country.frm format file created in the database directory, but the InnoDB storage engine itself stores the table data and index information else- where, in the InnoDB shared tablespace. The tablespace is used by multiple tables. That is, files for storing table contents are shared among tables.

But the MyISAM engine creates a data file and index file for each table. If Country is a MyISAM table, the MyISAM storage engine creates data and index files named Country.MYD and Country.MYI to store data rows and indexes (respectively) for the table.

0
"InnoDB stores its tables and indexes in a tablespace"

This means all InnoDB tables and indexes are stored in a tablespace before MySQL v5.6 all tables and indexes are stored in a shared table-space although option to change innodb_file_per_table was available

Prior to v5.6.6 This monolithic approach was targeted at machines dedicated entirely to database processing, with carefully planned data growth, where any disk storage allocated to MySQL would never be needed for other purposes. InnoDB's file-per-table mode is a more flexible alternative, where you store each InnoDB table and its indexes in a separate file. Each such .ibd file represents a separate tablespace. This mode is controlled by the innodb_file_per_table configuration option, and is the default in MySQL 5.6.6 and higher. In v5.6 or if you have set Innodb_file_per_table mode "ON" you will find .ibd and .frm entry for each table representing tablespace and table definition for each table respectively.

Not the answer you're looking for? Browse other questions tagged or ask your own question.