[wp-trac] [WordPress Trac] #55942: Add a "type" field to the meta and options tables

[WordPress Trac]

#55942: Add a "type" field to the meta and options tables
-------------------------------------------------+-------------------------
 Reporter:  azaozz                               |       Owner:  (none)
     Type:  enhancement                          |      Status:  new
 Priority:  normal                               |   Milestone:  Future
                                                 |  Release
Component:  Database                             |     Version:
 Severity:  normal                               |  Resolution:
 Keywords:  has-patch has-unit-tests needs-      |     Focuses:
  testing                                        |  performance
-------------------------------------------------+-------------------------

Comment (by rjasdfiii):

 I have been helping programmers and DBAs with MySQL performance for two
 decades.  A common design pattern is "Entity-Attribute-Value" (EAV), such
 as what WP uses for its various 'meta' tables.  EAV, and the lack of good
 indexing, is a big performance issue _today_ for WP.

 Most designers who discover (or re-invent) EAV come up with something like
 what WP has, but some carry the design pattern a step further:

 * Normalize the various keys and/or values -- I explain to them how much
 worse this is because of having to do an extra hop to get to the actual
 values.
 * Add a datatype to the "value".  This helps with numeric comparisons.
 The basic EAV fails to provide a simple way to compare numeric values, and
 they end up with "10" < "2".  Doing `CASTs` is a clumsy workaround.
 * Multiple columns -- one each for strings, floats, integers, dates, etc.

 For the last two designs, the user has already implemented it and is
 stumbling badly over performance issues.  I must tell them to rip out that
 embellishment and go to the basic EAV.

 So, I am telling you that your design, though inspired, will be a
 nightmare of coding clumsiness and performance degradation.

 _Be aware that almost any embellishment in this area will lead to the
 inability to use an `INDEX`_  That, alone, dooms any performance.  I claim
 that `postmeta` is already in deep-dodo because of performance.  This
 plugin helps by improving the `INDEXes`:  https://wordpress.org/plugins
 /index-wp-mysql-for-speed/ but leaves intact the inherent design problems
 of EAV.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/55942#comment:27>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform