Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
[nope] Change database field types in new installations and upgrades
Would there be any nasty side-effects or repercussions by switching the custom fields from VARCHAR(255) to TEXT in core releases? I understand that a VARCHAR field type is perhaps preferable for performance, but I’m wary of upping the VARCHAR length manually in case an upgrade resets it back to 255 and truncates my content. I’m using all 10 custom fields on a new site build and a few are getting very close (5-10 characters headroom) to the 255 character limit.
Also, textpattern/Excerpt is set to TEXT, while textpattern/Excerpt_html is MEDIUMTEXT. I understand HTML markup will add characters to a plaintext excerpt, but bumping the textpattern/Excerpt to MEDIUMTEXT would bring it in line with the other two ‘big’ areas for text entry (textpattern/Body and textpattern/Body_html), both of which are MEDIUMTEXT right now.
Finally, looking at r5680, if the URL limit is to be raised to 1000 characters then textpattern/url_title should be increased from VARCHAR(255); as of the current build (r5686) it’s still set to VARCHAR(255).
Last edited by gaekwad (2014-05-24 14:56:57)
Offline
Re: [nope] Change database field types in new installations and upgrades
gaekwad wrote:
Would there be any nasty side-effects or repercussions by switching the custom fields from
VARCHAR(255)toTEXTin core releases?
You for one can then no longer sort or select by custom field columns. Or if you do, you get I/O activity and slow queries. VARCHAR is fixed size, while TEXT, being a blob, isn’t.
Instead of the sorting happening in memory, MySQL writes temporary table to disks, orders it and returns results. Text columns also have more space requirement, they waste memory, both by the cache and the buffer. And you can’t really index the whole value either, and sorting only uses few first bytes rather than the whole value.
- When you index a BLOB or TEXT column, you must specify a prefix length for the index.
- If a TEXT column is indexed, index entry comparisons are space-padded at the end.
- Only the first max_sort_length bytes of the column are used when sorting. The default value of max_sort_length is 1024.
- Instances of BLOB or TEXT columns in the result of a query that is processed using a temporary table causes the server to use a table on disk rather than in memory because the MEMORY storage engine does not support those data types.
- The maximum size of a BLOB or TEXT object is determined by its type, but the largest value you actually can transmit between the client and server is determined by the amount of available memory and the size of the communications buffers.
- Each BLOB or TEXT value is represented internally by a separately allocated object. This is in contrast to all other data types, for which storage is allocated once per column when the table is opened.
Short answer is; no. We will never be changing to TEXT columns. We are actually looking into downgrading fields where possible.
I understand that a
VARCHARfield type is perhaps preferable for performance, but I’m wary of upping theVARCHARlength manually in case an upgrade resets it back to 255 and truncates my content. I’m using all 10 custom fields on a new site build and a few are getting very close (5-10 characters headroom) to the 255 character limit.
Feel free to up the limit yourself. If something bad happens, you can just restore from a backup. There are no real risks.
You are anyways taking backups before updating; the updater can easily fail and leave you with corrupted database. e.g. It can hit server’s query limit, or there might just be a write error.
Also,
textpattern/Excerptis set toTEXT, whiletextpattern/Excerpt_htmlisMEDIUMTEXT. I understand HTML markup will add characters to a plaintext excerpt, but bumping thetextpattern/ExcerpttoMEDIUMTEXTwould bring it in line with the other two ‘big’ areas for text entry (textpattern/Bodyandtextpattern/Body_html), both of which areMEDIUMTEXTright now.
Those fields host data-blobs and are never used for selecting rows or sorting. I have plans offering options to remove them from article/article_custom tags select statement too (by introducing select option). The wildcard column selecting and use of now() are the two major performance pitfalls in Textpattern. Both are messing up query caching.
For instance all “in-past” selections should use Textpattern’s lastmod instead. E.g. Posted <= now() should be Posted <= get_pref('lastmod'). This itself, alone, causes major difference. Selecting future articles on the other hand, well, that is always going to suck performance-wise (the docs should have warning about using time="future").
Finally, looking at r5680, if the URL limit is to be raised to 1000 characters then
textpattern/url_titleshould be increased fromVARCHAR(255); as of the current build (r5686) it’s still set toVARCHAR(255).
Actually, r5680 has no direct relation to URL title. The URL limit is for bombshelter; its checks for the full request URI and blocks requests that go over the limit.
Upping the url_title would be foolish (read: it would give everyone a reason to laugh at us), since it must be indexed and cached; article entries are selected by it. The URL title is only a small portion of the request URI. If you want URL titles longer than 255 characters, at least your article locations aren’t memorable.
Last edited by Gocom (2013-12-06 12:03:22)
Offline
Re: [nope] Change database field types in new installations and upgrades
Jukka, this is really helpful. Thank you for your reply.
Offline
#4 2014-05-19 00:04:10
- petrutz
- New Member
- Registered: 2013-11-07
- Posts: 4
Re: [nope] Change database field types in new installations and upgrades
Would there be any nasty side-effects or repercussions if downgrading custom fields to say varchar(10) or char(1)?
It would be really useful as I’m looking at some +90 custom fields and as per MySQL documentation, we would probably hit the maximum row size of 65,535 bytes.
Thank you!
Offline