jamesdlow

<p>BETA: Extend WordPress with MySQL database read replica support for greater speed and scalability<br />
This is a first release beta, and should only be used at your own risk</p>


Database Read Replicas

BETA: Extend WordPress with MySQL database read replica support for greater speed and scalability

PRESSMAN

Hiroshi Sekiguchi

kazunao

pmhirotaka

hommakoharu

<p>※ Table names have been changed since v2.0.Please delete the meta_json table and reactivate it in the options screen.</p>
<p>Are you having trouble with slow queries due to WordPress table structure?<br />
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.<br />
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.</p>
<h3>Features</h3>
<p>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.<br />
The effect will be noticeable especially when the number of records is large.<br />
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.<br />
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,<br />
and the ability to add virtual columns, add a The ability to create indexes. Indexes on virtual columns to make it even faster.</p>
<h3>Actual Behavior</h3>
<ul>
<li>You can create and delete json tables and triggers and data for each json table on the configuration page.</li>
<li>Create virtual columns and indexes for the json table you created in the configuration page.</li>
<li>If you add a virtual column to the json table in the configuration page, the SQL is converted to face the virtual column.</li>
<li>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.</li>
<li>The custom meta table automatically stores data using MySQL and MariaDB triggers.</li>
</ul>
<h3>Conversion Conditions</h3>
<ul>
<li>Both the value in meta_key and meta_value is specified.</li>
<li>If meta_compare_key is specified, only <code>=</code> or <code>EXISTS</code> are allowed.</li>
<li><code>suppress_filters</code> is false.</li>
</ul>
<h3>Custom meta tables generated by plugin</h3>
<p>The actual table names are prefixed with $table_prefix.<br />
* postmeta_beyond (for posts table)<br />
* usermeta_beyond (for users table)<br />
* commentmeta_beyond (for comment table)</p>
<h3>Columns in each table</h3>
<p>The actual table names are prefixed with $table_prefix.<br />
* postmeta_beyond<br />
    * post_id(int)<br />
    * json(json)<br />
* usermeta_beyond<br />
    * post_id(int)<br />
    * json(json)<br />
* commentmeta_beyond<br />
    * post_id(int)<br />
    * json(json)</p>
<h3>Example of storing data in JSON type column</h3>
<pre>{ "state": "Wisconsin", "city": "Winter" } </pre>
<h3>Results of Speed Measurement</h3>
<p>ex1 ) Create 100 posts and 50 meta data for each post. Combine them 9 times.</p>
<ul>
<li>
<p>postmeta_beyond</p>
<ul>
<li>Equal:0.04</li>
<li>IN:0.09</li>
<li>NOT IN:0.06</li>
<li>LIKE:0.03</li>
<li>NOT LIKE:0.03</li>
<li>BETWEEN:0.04</li>
<li>NOT BETWEEN:0.04</li>
</ul>
</li>
<li>
<p>postmeta</p>
<ul>
<li>Equal:4.72</li>
<li>IN:4.79</li>
<li>NOT IN:4.74</li>
<li>LIKE:0.64</li>
<li>NOT LIKE:0.66</li>
<li>BETWEEN:0.63</li>
<li>NOT BETWEEN:0.63</li>
</ul>
</li>
</ul>
<p>ex2 ) 10,000 posts, 1 meta per post. meta_key is category.<br />
      Add a virtual column(category) to postmeta_beyond and put an index,<br />
      and compare it to the case without adding a virtual column.</p>
<ul>
<li>
<p>postmeta_beyond(with virtual column)</p>
<ul>
<li>Equal:0.02</li>
<li>IN:0.35</li>
<li>BETWEEN:0.12</li>
<li>NOT BETWEEN:0.09</li>
</ul>
</li>
<li>
<p>postmeta_beyond(without virtual column)</p>
<ul>
<li>Equal:0.15</li>
<li>IN:1.92</li>
<li>BETWEEN:0.37</li>
<li>NOT BETWEEN:0.29</li>
</ul>
</li>
</ul>
<h3>Notes</h3>
<ul>
<li>You’ll need TRIGGER, SELECT, and UPDATE permissions for the target table and its columns to use the trigger feature of MySQL and MariaDB.</li>
<li>It makes changes to SQL only if <code>suppress_filters</code> is false.</li>
<li>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’.</li>
</ul>


Beyond Wpdb

※ 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 slo …

Database Read Replicas vs Beyond Wpdb