[wp-trac] [WordPress Trac] #14558: Separate Database Table Support for Custom Post Types

[WordPress Trac]

#14558: Separate Database Table Support for Custom Post Types
-------------------------------+------------------------------
 Reporter:  rahul286           |       Owner:  (none)
     Type:  enhancement        |      Status:  reopened
 Priority:  normal             |   Milestone:  Awaiting Review
Component:  Posts, Post Types  |     Version:
 Severity:  normal             |  Resolution:
 Keywords:  2nd-opinion        |     Focuses:
-------------------------------+------------------------------
Changes (by samjco):

 * type:  feature request => enhancement


Comment:

 == **I wonder if mysql table partitioning may help.**

 So partitioning allows for virtual tables to be made based on either a
 KEY, HASH, Column, list.
 This acts a view but without the drawback.

 **If plugin territory**
 I think having a simple button that fires to ALTER TABLE (wp_posts) and
 add in partitions based on the existing custom post types. For instance (I
 haven't tested):

 {{{#!php
 <?php
     global $wpdb;
 ?>

 <!-- Add HTML submit button here  -->
 <button type="submit" name="submit" href="?runpart=1">Submit</button>

 <?php

 if ( isset( $_POST['submit'] ) && $_GET['runpart'] == 1):

     //Find WordPress default post types
     $pt_count_default = 3;

     $table_name = $wpdb->prefix . 'posts';

     $pt_count_sql = "SELECT DISTINCT post_type FROM $table_name;";

     //Get Distinct Count of post types;
     $curr_pt_count = $wpdb->get_var($pt_count_sql);

     //Check if this is the first time running
     if(get_option('option_old_pt_count')):

         $old_pt_count = get_option('option_old_pt_count');

         //If any custom post types were removed
         if($curr_pt_count < $old_pt_count):

             //Stored post type count minus current count
             $curr_pt_count = $old_pt_count - $curr_pt_count;

             //Remove post type partitions no longer needed
             $sql = "ALTER TABLE $table_name COALESCE PARTITION
 $curr_pt_count;";

         endif;

         //If any new custom post types were added
         if($curr_pt_count > $old_pt_count):

             //Add new Partitions to wp_posts table (this may error out if
 not dropped first)
             $sql = "ALTER TABLE $table_name PARTITION BY HASH(post_type)
 PARTITIONS $curr_pt_count;";

         endif;

         //Update option field
         update_option( 'option_old_pt_count', $curr_pt_count);

     else:

         //Add new Partitions to wp_posts table
         $sql = "ALTER TABLE $table_name PARTITION BY HASH(post_type)
 PARTITIONS $curr_pt_count;";

         //Add option field
         add_option( 'option_old_pt_count', $curr_pt_count);

     endif;

     require_once ABSPATH . 'wp-admin/includes/upgrade.php';

     dbDelta( $sql );

 endif;
 }}}

 \\

 **If Core territory**
 Maybe the MySQL Partition should be included in core's database and table
 creation script.
 [https://dev.mysql.com/doc/refman/8.0/en/partitioning-types.html]

 For instance:

 {{{#!php
 <?php
 "
 CREATE TABLE" . $wpdb->prefix . "posts (
 ID      bigint(20) NOT NULL AUTO_INCREMENT,
 post_author     bigint(20),
 post_date datetime '0000-00-00 00:00:00',
 post_date_gmt datetime  '0000-00-00 00:00:00',
 post_content longtext,
 post_title      text,
 post_excerpt text,
 post_status     varchar(20),
 comment_status  varchar(20),
 ping_status     varchar(20),
 post_password varchar(20),
 post_name varchar(200),
 to_ping text,
 pinged  text,
 post_modified datetime '0000-00-00 00:00:00',
 post_modified_gmt datetime '0000-00-00 00:00:00',
 post_content_filtered longtext,
 post_parent     bigint(20),
 guid    varchar(255),
 menu_order      int(11),
 post_type       varchar(20),
 post_mime_type  varchar(100),
 comment_count   bigint(20)
 PRIMARY KEY  (ID)
 )
 PARTITION BY LIST COLUMNS(post_type) (
     PARTITION ppost VALUES IN('post'),
     PARTITION ppage VALUES IN('page'),
     PARTITION pcpt_1 VALUES IN('custom-post-type1'),
     PARTITION pcpt_2  VALUES IN('custom-post-type2')
 );

 ";
 }}}


 With using MySQL Partitions we can run select queries as:

 {{{"SELECT * FROM" . $wpdb->prefix . "posts PARTITION (pcpt_1);";}}}

  Or as normal. Will work both ways.

 {{{"SELECT * FROM" . $wpdb->prefix . "posts WHERE post_type = custom-post-
 type1;";}}}

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