0

I have a MySQL Barracuda table with about 150 fields and most of them are TEXT types.

  1. If I wanted to store more than 65,535 characters in a row, would it be possible to do so using Amazon's AWS database ? If so which kind of database ? RDS ?

  2. I am using a semi-VPS kind-of hosting company which has the application in vanilla PHP connected to this database. Would it be possible to migrate just the database to AWS and keep the application running on my hosting company ? (I should be able to connect to this AWS database from my host's PHP application.

Improve this question
1
  • Yes you can access a database at AWS over the internet, but you most likely should not. It will add lots of latency and might make the connection unstable.
    – eckes
    Jun 2, 2018 at 4:52

1 Answer 1

Reset to default
0

(Addressing Q1)

There are many workarounds for having bloated columns. Let's tackle that.

What is the table's ROW_FORMAT? DYNAMIC and COMPACT work differently.

Do they all need to be TEXT? Or can some be VARCHAR with low limits? (Eg: name TEXT --> name VARCHAR(60))

Consider "vertical partitioning". This is where you split a table into 2 tables -- putting some columns in each table. They would have the same PRIMARY KEY (except not both being AUTO_INCREMENT).

Do any of the columns have repeated values? If so, consider normalizing. (Eg, country TEXT -> country_code CHAR(2) CHARACTER SET ascii with values like 'US', 'ES', 'UK', 'IN'.)

Have you splayed an array across columns? That is a no-no. Turn that set of columns into rows in a related table. (Eg home_phone, work_phone, fax, cell.)

Please provide SHOW CREATE TABLE with the actual column names; we may have more hints. (And some of my suggestions above may be painfully obvious.)

After looking at the schema...

I see a bunch of columns that sounds like money or other numeric stuff, but is in VARCHAR(25); consider changing to INT or some other numeric datatype

I see about 80 each of TEXT and VARCHAR. This does stress some builtin limits in InnoDB. But they can, and need to be, worked around:

Plan A: Vertical partitioning -- break out FR, COP, PP, Customer, etc. into a few extra tables. Use JOIN to re-gather the data.

Plan B: Throw most of the columns into a large JSON text column. Or maybe one for FR, one for Customer, etc. Most app languages can easily parse JSON.

Since you have only one index (on id) I guess there is no call for searching the table? A few other columns, like maybe userID and name, should be kept in their own columns, and possibly indexed.

Improve this answer
7
  • Its DYNAMIC. Most of them are TEXT because it's a questionnaire kind of website. And now I am implementing TinyMCE for HTML formatting which is the reason for more characters (HTML tags).
    – anjanesh
    Jun 1, 2018 at 16:38
  • @anjanesh 150 columns? Do not splay an array (of questions? of answers?) across columns. Make another table with probably only one TEXT column and a couple of numbers (eg, question_id). Then multiple rows for multiple Answers.
    – Rick James
    Jun 1, 2018 at 16:50
  • @anjanesh - To discuss further, please provide SHOW CREATE TABLE.
    – Rick James
    Jun 1, 2018 at 16:51
  • pastebin.com/zmxS4cqa
    – anjanesh
    Jun 2, 2018 at 2:00
  • @anjanesh - I added to my Answer.
    – Rick James
    Jun 2, 2018 at 2:41

Your Answer

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

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