Explaining the file-per-table mode

InnoDB tables are stored in storage areas called tablespaces, which contain both data and indexes. In older MariaDB versions, by default, InnoDB used only one tablespace called the system tablespace. It also contains the change buffer, the doublewrite buffer, and by default, the undo log. This tablespace is physically stored in the ibdata file in the data directory of MariaDB (ibdata1, ibdata2, and so on). While the system tablespace still exists on the latest versions of MariaDB and can still contain all the tables, it is now possible to store each new table in a dedicated tablespace. Each tablespace created in this way is a separate file with the .ibd extension, which is located in a database subdirectory. This storage method is called the file-per-table mode. It is enabled by default since MariaDB 10.0.

The file-per-table mode is enabled by default. To enable or disable it, we can use the innodb_file_per_table system variable. When a table is created...