[wp-hackers] questioning the efficiency of using custom post types
mike at etchsoftware.com
Sun Oct 30 18:40:53 UTC 2011
One of the really non-intuitive things about relational DBs like MySQL is
that looking up data spread across more tables is less-efficient than fewer
tables with lots of rows. It got me at first too, but doing JOINs just
isn't fast, and more tables means more For an example of how efficient
long tables like WPs are, take a look at Brett Taylor's documentation of
The longer tables also give us a lot of flexibility and prevent errors.
It's odd until you get to know MySQL, and the coding to deal with
meta-table isn't simple. And, since these things are already solved in
WordPress, it's also why we use WP over-and-over and why a lot of good devs
contribute to the project.
For an example of flexibility:
If you had a table of restaurants and later on you wanted to store extra
info about ones that served coffee, then... In a multi-table system you
could add columns to the restaurants table, or add a new table just for
those cafes. In a meta-table system you would add a meta-row and then add
the rows to the data table.
On a live site the multi-table changes could cause locking and performance
issues while they're happening, and they might leave you with either empty
columns for non-cafes or a whole other table to join in. On a live site
with the meta-tables the update would be the same as any other insert and
performance would be almost unchanged.
For an example of preventing errors:
Let's say you make some changes on your dev server and need to apply them
to the live site. On a multi-table system this could be adding columns or
adding tables. On a meta-table system this would be adding rows.
While the update queries run on the live site, the multi-table system could
return no data when queries see columns or tables "missing" because they
aren't added yet. The meta-table will return the "old" data, so there may
be missing fields, but no errors.
In the event someone makes a mistake in the update process (which happens
way more than it should, unless you're doing test-migrations and have
staging, test, pre-deploy and/or other versions of the site)... The
multi-table system will return errors to live users up until the mistake is
corrected. The meta-table system just leaves a few fields blank and (as
long as your devs are checking whether or nat a value exists) live site
visitors should just see some empty fields on the live pages.
More information about the wp-hackers