Re: Suggestion for optimization - reg

[Rick James]

Hard to help you if you cannot change the schema.

> should I keep Default NULL or change to Default '0'.
MyISAM takes 1 bit represent a NULL column.
MyISAM takes 4 bytes to represent a 0 in an INT.
That's a factor of 32 in favor of NULL (at least for an INT).

> Probably the table conatains more than the maximum column numbers allowed in Innodb engine
That's another reason to consider moving columns into other tables.

> I do not have much knowledge about characterset. Kindly suggest a better character set.
It sounds like this database will contain information in only one language. Which language?
latin1 is fine for most of Western Europe and the Americas. utf8 is better for most of Asia. utf8 is essentially required if there is a mixture of languages, etc. For example, mixing Arabic and Korean would require utf8.

To redesign the schema, you could write a script to copy the records into new table(s), walking through table via the PRIMARY KEY. Some design work would be needed to deal with updates that come in during the migration. TRIGGERs is one way. Meanwhile, take a copy of the table(s) and experiment on another server.