0

I have made a mistake when designing my DB. I have created a table with more than 40 text cells. Now I have a nearly finished application (cakePHP) and I recognized that InnoDB tables have these limitations the first time while testing with larger data...

I read tons of forum threads and websites and all I find is the limit of InnoDB and that one 'solution' of this can be using MyIsam tables. But I can't find any information of the limits of row-size of MyIsam or if it has none. Can anybody answer me that?

I think I know most about the other differences between those two and I think don't need the features of InnoDB. I am only searching for an easy way to solve my problem of the row-sizes...

My second question: If I have to split my table in 4-6 tables (vertical partitioning) with less columns, does anybody know if cakePHP has a good way in dealing with this? Logically it should be one data-storage. Every SELECT statement has to fetch all the data from all the split tables. The tables will all have a 1to1-relation.

Steffen
  • 1
  • Using MyISAM is never a good solution. What about adjusting your schema to be more key-value in structure? Tables with very large numbers of `VARCHAR` columns are usually the wrong way to go about it. – tadman Aug 12 '13 at 16:30
  • My Problem is that most of the data can be edited by the user and the user (and me) can't tell how much space he will need. So the fields have to be text-fields and can't be limited. Also as I said: The whole data is loaded at once and displayed on one page. Maybe there is a way of loading it bit by bit with ajax but this would mean to change whole lots of code. This is why I am searching for an easier solution before considering other - maybe better - ways. BTW: Can you tell if MyISAM would help me in the row-size-question? – Steffen Aug 13 '13 at 11:52
  • MyISAM is a terrible database engine, though, and isn't really an option for a database of any significance. It's not transactional, some writes will lock the whole table, and has no journal, so if it crashes it could be corrupted and unrecoverable. The better approach is to make a more generic storage structure with records having N rows for storing the string data that you can `JOIN` in. – tadman Aug 13 '13 at 14:44
  • If performance isn't a concern, you can always switch to `TEXT` columns instead of `VARCHAR` as these are not stored within the row itself. – tadman Aug 13 '13 at 14:44
  • I am using `TEXT` right now - and the first 7xx Byte are stored in the row itself, so only 10 can be stored in one row with the max of 8000 Byte. The problem hasn't appeared till now because for testing I left many fields empty or only with a few words in it. But now with real live data the constraint throws errors... – Steffen Aug 13 '13 at 15:03
  • Create a table like `id INT PRIMARY KEY AUTO_INCREMENT, c_key VARCHAR(255), c_value VARCHAR(255)` where you can create N rows for each attribute you need to store. This is a little slower, but doesn't suffer from row-length limitations, and if properly indexed isn't too bad in practice. – tadman Aug 13 '13 at 18:45
  • Thank you for your time, but VARCHAR(255) wouldn't be enough for most of my fields. There are fields that get text to fill a hole Din-A4-page. So again: I NEED big TEXT fields and I need to know if there is a way to store 40 of them in one table-row or if I do have to split my table. I would be very greatful even for a quick and dirty version (maybe to switch to MyISAM) so I can let the customer test the software. I can do the table splitting and all the related changes in my code after that. – Steffen Aug 14 '13 at 08:32
  • If you're storing that much data, you should use `LONGTEXT` instead, but you also **must** use a key-value association table. You cannot expect any engine to handle piles of these columns in a single row. – tadman Aug 14 '13 at 14:43
  • @tadman: Can you give me a link that explains these "key-value association table"? – Steffen Aug 15 '13 at 09:30
  • I have splitted my tables now in a few smaller ones. I haven't found a better/easier way in the short time - and I recognized that with cakePHP it was easier than I thought it would be. But I'm not ready yet. I still have much code to check and change... – Steffen Aug 15 '13 at 09:32

0 Answers0