How to store my custom values?
When you dive into the so called Settings API (something so poor even the core doesn’t use it), you will probably get the advice to store all your data in one single option, not in many. And it sounds just logical: Less options are faster than more options, right?
MySQL doesn’t know anything about arrays, so we have to convert this data type into something MySQL understands: a string. WordPress will use the function
serialize() for that. An array like …
$array = array ( 'first_name' => 'Thomas', 'last_name' => 'Scholz' );
… will be stored as:
And when you fetch that value from the database, with
get_option() for example, WordPress will inspect and unserialize it if needed with
Yes, the plain MySQL query will be faster. But is it worth it?
- Serializing and unserializing will cost extra time.
- Your database content depends now on an external language. When you want to search for one entry in your array, sort by or replace a value, you cannot do that in a plain SQL query anymore. This violates the principle of Separation of concerns.
The next option is a custom table. This requires more efforts, and you have to write large parts of your user interface for yourself. On the other hand, you can fetch just the values you actually need, the table can use fine-tuned indexes, and you can change its structure easier. Stephen Harris has written a good tutorial about custom tables.
So when to use what?
- Use one single option with a serialized value if you know it will not grow indefinitely and you need most of its contents each time you use
get_option()– or if you have to store the state of an object.
- Use multiple separate options if you need to search in some of their values or perform other similar SQL operations on them.
- Use custom tables if you want to collect potentially large amounts of data (blocked IP addresses for example)
There is no golden rule that fits all use cases.
When you start writing a plugin, it isn’t always clear which way is the best. Store your plugin’s version number as an option, so you know when you have to convert existing data in an installation routine after an update when you change your mind.
And no matter what you do: always clean up on uninstallation; delete anything the user will not need anymore. Make it an habit to write that part before you finish the code to store the data.