2

Is there any option to store table index in separate .ibd file? something like file_per_table mode? Becuase my table is growing more than 200gb. Table .ibd file have index for that table also. so If index kept in another file, then table .idb file size will be reduced.

Thanks

Improve this question
5
  • 1
    And how would that help you? Because of transactions handling, the table and its indexes are bound together really tightly, so you could not manage those files separately.
    – jkavalik
    Commented Jan 4, 2016 at 9:37
  • @jkavalik. Yes i understood. As my table .ibd file is growing dally and now its size is more than 200GB. Windows server have some restriction on .ibd file size. So i'm in need of reducing table size.
    – doe
    Commented Jan 4, 2016 at 9:40
  • 1
    Ah, I see :( thats quite unfortunate.. I think it is not possible (but not 100% sure), but you might use some different tricks - partitioning can "split" the table to multiple ones by some key, further normalization (or just separating columns 1:1) could too.
    – jkavalik
    Commented Jan 4, 2016 at 9:45
  • I have not heard of a Windows limit on file size in over a decade. What version of Windows are you running?
    – Rick James
    Commented Jan 4, 2016 at 22:07
  • 1
    Please provide SHOW CREATE TABLE; there may be other ways to shrink the footprint.
    – Rick James
    Commented Jan 4, 2016 at 22:08

1 Answer 1

Reset to default
1

In theory it's possible. What you call a table in InnoDB is a B+tree index named PRIMARY. The key in the PRIMARY index is the primary key field(s).

All secondary indexes are B+tree indexes, too. The key would be the secondary index field(s). The value is the primary key. (See my slides to get more details http://www.slideshare.net/akuzminsky/indexes-in-my-sql-aleksandr-kuzminsky-https-twindbcom?ref=https://twindb.com/efficient-use-of-indexes-in-mysql/)

Although logically the PRIMARY and secondary indexes are separate they're still stored in one .ibd file (that's assuming innodb_file_per_table=ON). There is no way to store them in separate files.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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