Indexing MySQL Tables
3 posts by 2 authors in: Forums > CMS Builder
Last Post: May 6, 2021 (RSS)
By gversion - March 31, 2021
Hello,
I have been told that my database tables are not well structured. Columns being joined are of type medium text, which means they are not indexed and are going to perform really bad if the data increases.
Can anyone please advise whether changing the type of the columns will cause any issues with CMSB?
Thank you,
Greg
By daniel - May 5, 2021
Hi Greg,
While it can differ from case to case, changing column types should not generally be a problem with CMSB as long as the column type is suitable for the data in the field. For example, a field that contains only numbers (such as record num references) can pretty safely be changed from the default MEDIUMTEXT to an INT, which can improve performance for some lookups/joins. It is possible to do this within CMSB: when editing a field, under "Advanced Options" change the MySQL Column Type. You can also add an index to fields in this section by checking the "MySQL Indexing" option.
I would recommend making database backups before making changes like this, and if possible, testing them out in a staging environment.
Let me know if you have any other questions!
Thanks,
Technical Lead
interactivetools.com
By gversion - May 6, 2021
Hi Daniel,
That's useful to know, thank you for explaining. I have checked the num values and they seem to be int(10) unsigned so I think that looks OK.
However, I will review this in more detail and carry out some tests on a dev environment.
Thanks again,
Greg