2

I have a fairly large-scale site to build? How many custom fields can I use without running into MySQL database limitations?

  • 1
    How many do you think you'll need? I've got one site where one channel has 58 fields, another channel has 38 then another with 20 plus a few smaller ones with around 5-10 each. No issues whatsoever... yet! – foamcow Nov 27 '12 at 11:30
  • I've got about 160 fields planned. It's a migration from another site which had a fairly intense data structure. Will try refine before importing! – Richard Frank Nov 27 '12 at 19:41
16

From the MySQL docs:

"There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table."

Keep in mind that each field uses two columns, one for field data, another for field type. So divide the maximum column limit by two, and then subtract 3 for the entry_id, site_id, and channel_id columns; which leave you with the possibility of 2046.5 fields.

"Every table (regardless of storage engine) has a maximum row size of 65,535 bytes."

Here are full details: http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html

  • 1
    It's not quite as simple as that - As I understand it the MySQL limits are affected by the column types/lengths. Eg, if you have lots of custom fields that are dates, these take up more space than an INT(2). – James Smith Nov 27 '12 at 19:10
  • All custom fields in EE are stored as TEXT, unless you make changes to the backend database. – Isaac Raway Nov 28 '12 at 5:37
  • When you use Matrix, your field's contribution to row_length is greatly reduced. – Ian Pitts Dec 4 '12 at 16:55
  • I just added a 10th site to my EE 1.6 MSM and it threw and error saying I had maxed out the number of columns available. It maxed out at the 507th field. – Chris Feb 27 '15 at 20:29
  • @Chris How many total columns in the table that is maxed out? – Anna_MediaGirl Feb 27 '15 at 20:48
2

Back in the EE1 days I remember some people hitting the limit at around 750 custom fields - usually caused by ambitious Multi-Site-Manager installations. I think they made some improvements in EE2 that may have pushed that a little higher, but either way unless you're using MSM it shouldn't be a problem even for massive sites.

2

I have maxed out at between 800-900 custom fields per EE install. This is on EE 1.6.7 and is an MSM installation with about 9 sites so not all of those custom fields are for a single site.

1

I'm guessing you know this but EE adds a new column for all entries with every new field (exp_channel_data). So if you had many field groups with many fields this table might hit MySQLs limit, but 2046 ((4096 - 3) / 2) fields is quite extreme and I'd say you need to reconsider your site structure if you need this many! The most I've used is a few hundred, including matrix fields, on a very complex site.

MediaGirl and James have posted the answer, but it's something that shouldn't be an issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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