1

How to split the huge mysql table (size around 200GB) internally? Is there any mechanism other than partition? The table size is daily growing. Table created using innodb with indices. Thanks in advance.

Table have around 30 columns. Most of the time, i use select * as i need to show all the field. Then there is no problem in fetching data. Problem is size of table. ibd file size is now around 200gb. There are many tables like this. So i need to split the table internally as i don't have access to modify the code.

Improve this question
5
  • You might want to look at this.
    – Vérace
    Commented Dec 30, 2015 at 10:04
  • @Vérace, thanks for the link. but this is related with mysql products. Is there any mechanism without using additional resources?
    – doe
    Commented Dec 30, 2015 at 10:42
  • There are too many unknowns to give "one-size-fits-all" advice. Please provide SHOW CREATE TABLE and some of the important SELECTs.
    – Rick James
    Commented Dec 30, 2015 at 18:44
  • Also, do you ever DELETE or UPDATE rows after they are inserted?
    – Rick James
    Commented Dec 30, 2015 at 18:45
  • Table have around 30 columns. Most of the time, i use select * as i need to show all the field. Then there is no problem in fetching data. Problem is size of table. ibd file size is now around 200gb. There are many tables like this. So i need to split the table internally as i don't have access to modify the code.
    – doe
    Commented Dec 31, 2015 at 3:33

1 Answer 1

Reset to default
2

There is few ways to do it as per below-

1) partitioning but you don't want it. Also need to remember that partitioning does not support with foreign keys.

2) You can normalize your table if there is duplicate values in table.

3) If this table contains transactional data or logs then you can move old data in historical tables year wise etc if possible in your environment.

Improve this answer
1
  • zafar. thanks. Normalization already done. Also, i can't split table manually as it required code change. Is there any other possibilities to handle huge data?
    – doe
    Commented Dec 30, 2015 at 6:38

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.