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.

migrated from stackoverflow.com Dec 30 '15 at 8:39

This question came from our site for professional and enthusiast programmers.

  • You might want to look at this. – Vérace Dec 30 '15 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 Dec 30 '15 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 Dec 30 '15 at 18:44
  • Also, do you ever DELETE or UPDATE rows after they are inserted? – Rick James Dec 30 '15 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 Dec 31 '15 at 3:33
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.

  • 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 Dec 30 '15 at 6:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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