內容簡介
自 v2.0 以後，表格名稱已更改。請刪除 meta_json 表格並在選項畫面中重新啟用。
你是否因為 WordPress 表格結構而遇到緩慢的查詢問題？
此外掛使用 MySQL 的特性之一，即 JSON 型別欄位，協助你加速網站，同時保持 WordPress 開發的靈活性，避免複雜查詢。
你也可以在組態頁面中新增虛擬欄位，並加上索引，以加快速度。
特性
如果一篇文章/使用者/評論中有大量的元數據和複雜的元查詢，將會產生大量的內部連接，拖慢查詢的執行速度。
當記錄數量大時，效果尤為顯著。
此外掛可避免複雜的連接，透過在自訂元數據表中的 JSON 型別欄位中存儲所有單篇文章、使用者或評論的元數據，防止對執行速度的減慢。
此外掛的組態頁面包含一個檢查 group_concat_max_len 的功能，一個用於測量元數據表及其自訂元數據表之一致性的函數，和新增虛擬欄位和建立索引的功能，以加快速度。
實際行為
你可以在設定頁面上建立和刪除 JSON 表格和觸發程序，以及每個 JSON 表格的數據。
在設定頁面上為建立的 JSON 表格創建虛擬欄位和索引。
如果你在設定頁面上為 JSON 表格新增了虛擬欄位，則 SQL 會轉換成面向虛擬欄位。
將 WordPress 預設 API 的 get_posts、Wp_Query 等產生的 SQL 轉換為一個與其自身元數據表結合的簡單 SQL。
自訂元數據表會使用 MySQL 和 MariaDB 觸發程序自動存儲數據。
轉換條件
meta_key 和 meta_value 中均指定了值。
如果指定了 meta_compare_key，則僅允許使用 = 或 EXISTS。
suppress_filters 為 false。
此外掛自動產生的自訂元數據表
實際表格名稱的首碼為 $table_prefix。
* postmeta_beyond（針對文章表）
* usermeta_beyond（針對使用者表）
* commentmeta_beyond（針對評論表）
每個表格中的欄位
實際表格名稱的首碼為 $table_prefix。
* postmeta_beyond
* post_id(int)
* json(json)
* usermeta_beyond
* post_id(int)
* json(json)
* commentmeta_beyond
* post_id(int)
* json(json)
存儲資料的 JSON 型別欄位範例
{ "state": "Wisconsin", "city": "Winter" }
速度測量結果
範例1）建立100篇文章，每篇文章有50個元數據，進行9次合併。
postmeta_beyond
等於:0.04
IN:0.09
NOT IN:0.06
LIKE:0.03
NOT LIKE:0.03
BETWEEN:0.04
NOT BETWEEN:0.04
postmeta
等於:4.72
IN:4.79
NOT IN:4.74
LIKE:0.64
NOT LIKE:0.66
BETWEEN:0.63
NOT BETWEEN:0.63
範例2）10,000篇文章，每篇文章有1個元數據，meta_key 是 category。
新增一個虛擬欄位，並將其加入 MySQL 建立的索引。
當查詢 meta_key 為 category，且 meta_value 為 'cat1' 時，此外掛運行速度比 WordPress 慣用方法快 400 倍以上。
外掛標籤
開發者團隊
原文外掛簡介
※ Table names have been changed since v2.0.Please delete the meta_json table and reactivate it in the options screen.
Are you having trouble with slow queries due to WordPress table structure?
This plugin helps your site speed up using JSON type columns, one of the features of MySQL, and prevents complex queries while maintaining the flexibility of WordPress development.
You can also add a virtual column in the configuration page and index the virtual column to make it even faster.You can also add a virtual column in the configuration page and index the virtual column to make it even faster.
Features
If a large amount of meta data is given to 1 post/user/comment and a complex meta_query is specified, a large number of inner joins will occur, which will slow down the execution of the query.
The effect will be noticeable especially when the number of records is large.
This plugin avoids complex joins and prevents slowdowns by storing all the meta data for 1 post/user/comment in a JSON type column in a custom meta table.
The dedicated configuration page includes a check for group_concat_max_len, a function to measure consistency of meta table and its custom meta table,
and the ability to add virtual columns, add a The ability to create indexes. Indexes on virtual columns to make it even faster.
Actual Behavior
You can create and delete json tables and triggers and data for each json table on the configuration page.
Create virtual columns and indexes for the json table you created in the configuration page.
If you add a virtual column to the json table in the configuration page, the SQL is converted to face the virtual column.
Converts the SQL that WordPress auto-generates when using the default APIs of get_posts, Wp_Query, and etc. into a simple SQL that combines the posts/users/comment table with its own meta table.
The custom meta table automatically stores data using MySQL and MariaDB triggers.
Conversion Conditions
Both the value in meta_key and meta_value is specified.
If meta_compare_key is specified, only = or EXISTS are allowed.
suppress_filters is false.
Custom meta tables generated by plugin
The actual table names are prefixed with $table_prefix.
* postmeta_beyond (for posts table)
* usermeta_beyond (for users table)
* commentmeta_beyond (for comment table)
Columns in each table
The actual table names are prefixed with $table_prefix.
* postmeta_beyond
* post_id(int)
* json(json)
* usermeta_beyond
* post_id(int)
* json(json)
* commentmeta_beyond
* post_id(int)
* json(json)
Example of storing data in JSON type column
{ "state": "Wisconsin", "city": "Winter" }
Results of Speed Measurement
ex1 ) Create 100 posts and 50 meta data for each post. Combine them 9 times.
postmeta_beyond
Equal:0.04
IN:0.09
NOT IN:0.06
LIKE:0.03
NOT LIKE:0.03
BETWEEN:0.04
NOT BETWEEN:0.04
postmeta
Equal:4.72
IN:4.79
NOT IN:4.74
LIKE:0.64
NOT LIKE:0.66
BETWEEN:0.63
NOT BETWEEN:0.63
ex2 ) 10,000 posts, 1 meta per post. meta_key is category.
Add a virtual column(category) to postmeta_beyond and put an index,
and compare it to the case without adding a virtual column.
postmeta_beyond(with virtual column)
Equal:0.02
IN:0.35
BETWEEN:0.12
NOT BETWEEN:0.09
postmeta_beyond(without virtual column)
Equal:0.15
IN:1.92
BETWEEN:0.37
NOT BETWEEN:0.29
Notes
You’ll need TRIGGER, SELECT, and UPDATE permissions for the target table and its columns to use the trigger feature of MySQL and MariaDB.
It makes changes to SQL only if suppress_filters is false.
This plugin sets 4294967295 to group_concat_max_len when it create an original table with JSON type column. You can change the number by using filter ‘beyond_group_concat_max_len’.
