How much string content can I store in an option?

1 Views

I am about to create a new plugin that fetches remote content and stores it locally for use on the WP website. I have a free plugin that does this with twitter and tweets, and I store a JSON file in the plugin directory. Some users complain that the plugin cannot write files to the folder due to permissions.

For this reason, I have been thinking about storing the remote content for this new plugin in an option. Is 250kb of HTML too big for an option? Is there another API call that can help me store a large chunk of string data?

Post Views: 1

Related posts

Leave a Reply

3 comments

  1. Look at the table schema in wp-admin/includes/schema.php:

    // regular blog tables
CREATE TABLE $wpdb->options (
  option_id bigint(20) unsigned NOT NULL auto_increment,
  option_name varchar(64) NOT NULL default '',
  option_value longtext NOT NULL,
  autoload varchar(20) NOT NULL default 'yes',
  PRIMARY KEY  (option_id),
  UNIQUE KEY option_name (option_name)
) $charset_collate;

// Multisite options
CREATE TABLE $wpdb->sitemeta (
  meta_id bigint(20) NOT NULL auto_increment,
  site_id bigint(20) NOT NULL default '0',
  meta_key varchar(255) default NULL,
  meta_value longtext,
  PRIMARY KEY  (meta_id),
  KEY meta_key (meta_key),
  KEY site_id (site_id)
) $charset_collate;

    In both cases the value is longtext. The MySQL manual says about that type:

    A TEXT column with a maximum length of 4,294,967,295 or 4GB (232 – 1) characters. The effective maximum length is less if the value contains multi-byte characters. The effective maximum length of LONGTEXT columns also depends on the configured maximum packet size in the client/server protocol and available memory. Each LONGTEXT value is stored using a 4-byte length prefix that indicates the number of bytes in the value.

    4GB. If that is not enough improve the clean-up process. Maybe you should store the content as a file anyway – in the uploads directory.

  2. It’s a longtext field…

    mysql> describe wp_options;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| option_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| option_name  | varchar(64)         | NO   | UNI |         |                |
| option_value | longtext            | NO   |     | NULL    |                |
| autoload     | varchar(20)         | NO   |     | yes     |                |
+--------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

    …and that means it can hold about 4 gigabytes. https://stackoverflow.com/a/4294527/751089

    IMHO this is a perfectly good use for an option as the information isn’t related directly to eg a post or a user. In general it’s not a good idea to store masses of data in one field, but then tweets are very replaceable.

    Finally, for storing tweets and other changing data that’s a bit slow to get hold of you may want to look at the Transients API, which can assist you with refreshing them at intervals.

  3. While it’s true that it’s a LONGTEXT column, which can theoretically hold up to 4GB, you also need to take into account MySQL’s “max_allowed_packet” size.

    The setting “max_allowed_packet” limits how much data can be sent in a single MySQL query and its default value is only 16MB.

    Most shared hosts won’t let you change that value. And even then, the maximum value you can set in MySQL is 1GB.

    So for a plugin that you hope will work on most shared hosts, WP options shouldn’t be larger than 16MB.