[wp-trac] [WordPress Trac] #60950: JSON serialization for meta values

[WordPress Trac]

#60950: JSON serialization for meta values
--------------------------------+------------------------------
 Reporter:  inf3rno             |       Owner:  (none)
     Type:  feature request     |      Status:  new
 Priority:  normal              |   Milestone:  Awaiting Review
Component:  Options, Meta APIs  |     Version:
 Severity:  normal              |  Resolution:
 Keywords:  reporter-feedback   |     Focuses:  performance
--------------------------------+------------------------------

Comment (by inf3rno):

 Looks like it will require a different table structure, because it is a
 lot more optimized when the entire column has JSON type:
 https://dev.mysql.com/doc/refman/8.0/en/json.html

 We have several options here:

  - two meta tables with the current structure except the second table has
 JSON column type for meta_value

 {{{
  meta_key   | meta_value
  package_id | s:3:"345";
 }}}
 {{{
  meta_key   | meta_value
  address    | {"city": "New York", "street": "Broadway", "number": 128}
 }}}

  - one meta table, but having a separate column for JSON values:

 {{{
  meta_key   | meta_value  | meta_json_value
  address    | NULL        | {"city": "New York", "street": "Broadway",
 "number": 128}
  package_id | s:3:"345";  | NULL
 }}}

  - one meta table and storing everything in JSON

 {{{
  meta_key   | meta_value
  address    | {"city": "New York", "street": "Broadway", "number": 128}
  package_id | "345"
 }}}

 It is hard to decide whether we need this without measuring. First we need
 to measure whether or how a bloated or very bloated database affects
 performance of meta queries. Second we need to measure how certain
 solutions and JSON serialization and unserialization affects performance.
 Maybe we don't need this at all, maybe the queries are faster with JSON,
 but the unserialization is a lot slower than PHP, I am not sure. I am not
 an expert of PHP + MySQL benchmarking, so we need somebody here, who can
 measure this.

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