Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This question already has an answer here:

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 :)

share|improve this question

migrated from Mar 6 '14 at 4:06

This question came from our site for professional and enthusiast programmers.

marked as duplicate by RolandoMySQLDBA, Paul White, Mark Storey-Smith, Kin, Jon Seigel Apr 21 '14 at 18:34

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

up vote 2 down vote accepted

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

share|improve this answer

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 by the 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.

share|improve this answer
"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.

share|improve this answer

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