Is it possible to have a table and its index on different drives?

All we need is an easy explanation of the problem, so here it is.

I’m running an Ubuntu MySQL server that has HDD and SSD internal drives. The HDD has larger capacity, but of course the SSD has much faster read/write speeds.

Is it possible for me to hold a (2.5 billion record and growing) table, and build an index for it, with the index on an SSD drive? I’m thinking this might allow me to make use of the huge HDD storage I have available, but improve indexed read speeds with the SSD.

I’ve read through a lot of the MySQL documentation online but can’t find the relevant page. I had heard somewhere that this was possible!

Any help, much appreciated. I am constrained to using MySQL, I can’t be installing other db systems or making changes to the server(s).

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Why they can’t be separated

With ENGINE=InnoDB, the data’s BTree (sorted by the PRIMARY KEY) and all secondary indexes (again BTrees) are stored in the same ‘tablespace’.

A tablespace is implemented as a file. Therefore, it is not possible to separate them. (MyISAM is different, but let’s not go there.)

A tablespace is manifested in one of 3 ways, none of which help with your question:

  • The ‘system’ tablespace ibdata1. Actually there can be multiple files, but you have no control over which blocks go in which file.
  • A .ibd file for the one table (and its indexes). Cf innodb_file_per_table.
  • A user-generated "tablespace".

And no advantage

Why split the index and the data? It is essentially useless, even with different speed drives.

  • When a query uses an index, it will first use the index and then use the data — thereby (usually) making the processing "serialized". This is a reasonable argument against splitting index from data across two same-speed disk drives.
  • All blocks–data or index–are cached in the buffer_pool in RAM. The index is often smaller, hence more easily cached, hence more likely to be in RAM, regardless of the disk location.

Method 2

I’ve read through a lot of the MySQL documentation online but can’t find the relevant page.

The most relevant pages are:

Creating InnoDB Tables

InnoDB tables are created in file-per-table tablespaces by default. To create an InnoDB table in the InnoDB system tablespace, disable the innodb_file_per_table variable before creating the table. To create an InnoDB table in a general tablespace, use CREATE TABLE … TABLESPACE syntax.

Resume – there is 3 options. The table may posess in system (1), general (2) or file-per-table (3) tablespace.

  1. The System Tablespace

The system tablespace is the storage area for the change buffer. It may also contain table and index data if tables are created in the system tablespace rather than file-per-table or general tablespaces. In previous MySQL versions, the system tablespace contained the InnoDB data dictionary. In MySQL 8.0, InnoDB stores metadata in the MySQL data dictionary. See Chapter 14, MySQL Data Dictionary. In previous MySQL releases, the system tablespace also contained the doublewrite buffer storage area. This storage area resides in separate doublewrite files as of MySQL 8.0.20.

The system tablespace can have one or more data files. By default, a single system tablespace data file, named ibdata1, is created in the data directory. The size and number of system tablespace data files is defined by the innodb_data_file_path startup option.

Resume – system tablespace is a file or a group of files where a lot of objects is stored without dividing to "one file per object", and you cannot affect on what part of what object in what file will be placed into.

  1. General Tablespaces

After creating a general tablespace, CREATE TABLE tbl_name ... TABLESPACE [=] tablespace_name or ALTER TABLE tbl_name TABLESPACE [=] tablespace_name statements can be used to add tables to the tablespace, …

Resume – the table is added into general tablespace as one solid object, without dividing to data and index parts.

  1. File-Per-Table Tablespaces

A file-per-table tablespace contains data and indexes for a single InnoDB table, and is stored on the file system in a single data file.


Totally: there is no way to store data part and index part of the InnoDB table separately.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply