4

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?

3 Answers 3

13

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.

1
  • 1
    This doesn't take into account MySQL's "max_allowed_packet" size, whose maximum value is 1GB, but default value is only 16MB (which is what most shared hosts use.) Jan 1, 2020 at 23:07
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.

0
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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