Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

When developing plugins that requires data storage, what's the pros and cons of using one method or another ?

The explanation given in the codex is not detailed:

Before jumping in with a whole new table, however, consider if storing your plugin's data in WordPress' Post Meta (a.k.a. Custom Fields) would work. Post Meta is the preferred method; use it when possible/practical.

share|improve this question

4 Answers 4

up vote 16 down vote accepted

Well, if I take the hat of a WP script kiddie, my answer would be: use post_meta, always.

However, I happen to know a thing or two about databases, so my answer is: never, ever, ever, use an EAV (aka the post_meta table) to store data that you might to need to query.

On the index front, there are basically none worth using in meta tables. So, if you're storing data type XYZ and are hoping you query all posts that have XYZ with a value of 'abc', well... good luck. (See all of the users/roles/caps related tickets in the WP trac to give you an idea of how gory it can get.)

On the join front, you quickly crash into the limit at which the optimizer decides to use a generic algorithm instead of analyzing the query when there are multiple join criteria.

Thus, no, no, no, no. Don't ever, ever, ever use a meta. Unless what's you're storing is cosmetic and will never be part of a query criteria.

It breaks down to your app. If you're storing, say, the birthdate of a movie director, than big deal. Use a meta all you want. But if you're storing, say, the release date of a movie, you'd be nuts to not use a separate table (or adding columns to the posts table) and add an index to that column.

share|improve this answer
1  
Yes, the plugins that i'm developing are handling custom data like events, news, press releases, Job offers ... From outside "WordPress World", using tables is not really an option. But the advice from the WordPress Codex, is a little bit confusing. How can serialized chunks of data be preferred to normalized/structured/indexed data? –  Nassif Bourguig Dec 3 '10 at 21:35
1  
If you ask the average WP dev, he'll likely answer "use a meta" or "use a taxonomy". And I agree, up to the point where you need to query against it. If so, and I believe it is your case, my only answer is, add the fields to the posts table, or create a separate table entirely. Else you're in for enormous performance issues when it comes to querying and, even more importantly for lists of nodes, top-n sorting. –  Denis Dec 3 '10 at 21:38
1  
Denis would you be able to elaborate on this a little more, I find it very informative but would love some more data, has anyone done tests?, what exactly are the major drawbacks and limitations, thanks. –  Wyck Dec 4 '10 at 6:49
4  
@Denis - Quite the passionate advocacy against postmeta, eh? You know you are going firmly against the orthodoxy and you'll fall out of the good graces of the high priests of church of code poetry if you persist in such talk, don't you? :-) But seriously don't you think you overstate just a bit? It really depends on if there are going to be tens of thousands of meta records or not. In many cases there simply are not enough records to worry about. One complex site I'm deploying has around 10,000 meta records with few new records planned, and it's fine (fyi, it's not a blog.) –  MikeSchinkel Dec 4 '10 at 8:52
1  
@Denis - Thanks for the comments. And don't get me wrong, I probably lean a lot more towards your perspective but the combination of 1.) an hour-long debate with Matt at WordCamp Birmingham over the merits of Pods-like fields and 2.) the simplicity of meta has be resign to focus my attentions on other issues I could potentially change. At WCB I came away realizing as long as Matt is in charge it won't change because (my guess is) Matt is so enamored with idea of fewer tables he won't allow himself to recognize the down sides of indexing on a 768 byte key. <sigh> –  MikeSchinkel Dec 4 '10 at 9:39

It depends on what you're doing. The WP way is to use the existing tables, as they've been designed to be flexible enough, however occasionally you'll reach a new class of data that can't be placed in an existing table, e.g. if you wanted category meta data, you could choose to create a wp_termsmeta table.

However, usually you can store your data quite comfortably in the different tables that exist, and where you store your data depends on what your plugin does.

  • For general plugin settings, use the get_option() API call - this will be cached also.
  • For plugin settings that a particular to an individual post, then use the custom meta data per post with get_post_meta(). This is usually plenty for what you need.

Caching is implemented within WordPress to speed up your response time also.

share|improve this answer

For my FarmVille site :) I did both but never finished it because I sold it:

  1. I read the farmville xml and dumped the data in a custom table
  2. In WordPress I had custom fields automatically made for every field in that table (and some extra)
  3. Now worry on what happens if a value changes in either the table or on the other side: the custom field since they need to be continuously in sync

I did this because I wanted on the one hand have users edit the wordpress site by entering new farmville data e.g. "a cow costs 10 coins" BUT from the integration side: IF a change in the xml ment the cow now costs "20 coins" (via the front-end editing plugin) that would be given as option after it: so that either the XML OR the user was right (sort of wiki system).

So here is an example when using both.

share|improve this answer

agreed with denis 100%. But there is a way around it.

The problem with using the post meta for values to be querried is when the values are array's etc. Such as this:

array(
'key1' => 'val 1',
'key2' => 'val 2'
);

This gets stored in the db as a serialised string, which will look something like this:

{array["key1"]...{}...}

So when you want to query all posts with array['key2'] = 'val 2' then wp has to pull every meta entry called array, unpack it, then test it, then go to the next. This will definately bring down your server if your site is successfull and has lots of posts, pages, custom posts etc.

The solution is project depending, and you'll see why. If you were to store the data as a var = val then wp will be able to search without having php to unpack every single test. To do this in the scenario above you would use some namespacing and store the meta keys:

_array_key1 = 'val 1';
_array_key2 = 'val 2';

then wp looking for key 2 with val 2 will be able to pull it straight away. This is project depending though. My current project relies on about 20 different dataTypes to be store with each custom post so the above would just create a massive table to search, seeing as how we are expecting 100's of thousands of posts. So in that scenario a custom table is the only way.

Hope this helps someone

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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