- Run EXPLAIN on all content_type_ and content_field_ tables
- Identify longtext fields (others later?)
- Analyze max(length(field)), add % buffer to the maximum length found
- Use CCK/Field API to ALTER fields accordingly

.. and while I'm having pie for dinner..

- The 'age' of a content type (span between creation date of first and last nodes of that type)
- The presence of that field in Views (especially as a filter)
- The occurrence of that field name in the slow query log

Could all add 'weight' to a recommendation of action.

Jason

Comments

mikeytown2’s picture

mikeytown2 commented

- Already run explain on all views that take longer then 10ms to run.
- Issue for text fields #973380: Optimize usage of TEXT columns
- What would "add % buffer to the maximum length found" do for us?
- Using the field API to alter fields might be the golden ticket if it allows sizes above 256 for things other then text fields.

- What's the advantage of knowing the age of a content type?
- This module already figures out what CCK fields are used in a view filter and can ad an index to it.
- Haven't done much with a slow query log analyzer. There is lower hanging fruit to pick before this one.

Use the latest dev if you haven't already.

jason.fisher’s picture

jason.fisher commented

In my case, an optimization project on a site with several dozen text fields that had no 'maximum size' set was the biggest issue. i.e., a "full name" field was longtext because of this.

I used an EXPLAIN on the content_* tables, grepped for longtext, then located the maximum length actually used by data in that field using MAX(LENGTH()). Because the site had been in operation for several years and had many nodes in place, I was reasonably confident in the largest size used to date + 10-20% as the new maximum.

Then, I would update the fields in the content types manually to set this value. If the maximum length fits into a varchar (most did), then CCK will ALTER it gracefully.

This allowed them to completely remove longtexts from several tables/content types.

Regards,
Jason