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