WordPress Custom Fields
Often when you read about WordPress custom fields what is actually being referred to are the meta tables in the WordPress database. Three tables are provided for storing meta data about some object or table types in the WordPress ecosystem. In my post What’s the Meta in WordPress I recommend using the meta tables for storing your own custom data, but what if you have a large number of custom fields in which you want to store data?
The issues with using the meta tables.
Even though the established method of storing custom fields is in the meta tables there are times when this might not be the optimal solution. Using the meta data tables for storing your own data is great if the number of extra fields you want to store is small, maybe 3 or 4. Once the number of fields are more than this then using the meta tables can make maintenance, performance tuning and troubleshooting more difficult.
The main issue with storing meta data in the meta tables when you have a large number of extra fields is that you lose opportunities for optimising your data. The meta data tables are set up in a very generic way to store any piece of data. Let’s take a quick look at the postmeta table.
|
1 2 3 4 5 6 |
CREATE TABLE `wp_postmeta` ( `meta_id` bigint(20) UNSIGNED NOT NULL, `post_id` bigint(20) UNSIGNED NOT NULL DEFAULT '0', `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `meta_value` longtext COLLATE utf8mb4_unicode_ci ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; |
As you can see the meta_key and meta_value columns are varchar and longtext respectivly and are setup in this way to make the postmeta table very flexible. However this does reduce the options for making the table more performant as we will see in the next section.
Performance
Table performance is not something that will impinge on your WordPress site very often unless it is very large. But as the amount of data you have grows then need to improve the performance will arise. Because the data in the meta_key column has a good chance of not being very unique, the index on this column may not be used and and table scan would then take place to find the data you are looking for. This could potentially have a negative impact on performance.
Also as you add more custom fields to your meta table, the meta table get’s bigger and this could potentially have an impact on the performance of the table. Granted MySQL(the database used by WordPress) can handle millions of rows, but if you have a lot of data you may find those tables filling up more quickly than you think. Over a couple of years the data can build up to quite an impressive amount.
Field changes
If you decide to add a new custom field to your dataset then it is simply a matter of adding a new value/key pair right? Well maybe not. If you have a large number of rows in the parent table to add custom fields for then you may well have to add your new fields for each and every row in the parent table to the meta table where you are storing your custom field data.
Troubleshooting
If you have a problem with your data and you have to dig through the tables manually, then finding the piece of data you want to inspect or change can be a bit of a nightmare. You may require multiple joins in your SQL and correlated subqueries to get the view of the data you need. Unless you are a SQL Wizard then writing the SQL for building this type of query can be quite a challenge.
Using your own custom tables
Another approach would be to use your own custom tables. By using a custom table you have control over performance, making changes to the fields, and troubleshooting becomes a lot easier.
The use of WP_Query with custom tables
There is a downside to using your own custom tables. If you are using WP_Query in the ‘WordPress Way’ to get data from the database then as soon as you start adding custom tables into the mix you have to add extra plumbing to get the data from your custom table, or to include it in the results from WP_Query.
Why use WP_Query at all?
By using WP_Query you are abstracting away the details of how WordPress gets the data from the database. If you are a full stack developer(you can write code for the front end, backend and SQL) then this is less of an issue. However if the code has been written using well known WordPress approaches then the learning curve for another WordPress developer becomes a lot less steep.
There are a couple of approaches to this which I will go into more detail in another post, but let’s assume your are storing custom data about posts stored in the post table. Instead of using the postmeta table you have a custom table with various columns, but also included is the post_id. Some ideas for getting your data into your PHP code could include:-
- Write a custom query to search the custom table and then use the returned post id’s in the query to use WP_Query on the posts table. Although if you are going down this route you may as well use the next point,
- Write a completely custom query to get all the data back,
- Hook into filters fired when a query is run such as ‘posts_where’ and ‘posts_join’ amoungst others to modify the query being sent to the server.
Conclusion
So should I use custom tables or meta tables? As always when writing functionality for your site, the answer comes down to ‘it depends’. If you are only storing a limited number of custom fields then I would still recommend using the meta tables. However if you have a large number of custom fields to implement, you might want to think about using a custom database table to start with. If you want to explore the issue further then there are a number of interesting blog posts on the topic :-
