add_clean_index( string $table, string $index ): true

User Contributed Notes

1. Skip to note 3 content

   MakeWebBetter 6 years ago

   You must log in to vote on the helpfulness of this noteVote results for this note: 0You must log in to vote on the helpfulness of this note

   Sometimes after a table has been created in a database, we find that it is advantageous to add an index to that table to speed up queries involving this table.

   function wpdocs_mwb_make_fetch_fast()
   {
       global $wpdb;
       // Add some Clean up indices
       add_clean_index( $wpdb->posts, 'post_name' );
       add_clean_index( $wpdb->categories, 'category_nicename' );
       add_clean_index( $wpdb->comments, 'comment_approved' );
       add_clean_index( $wpdb->posts, 'post_status' );
   }
2. Skip to note 4 content

   nimlab 1 year ago

   You must log in to vote on the helpfulness of this noteVote results for this note: 0You must log in to vote on the helpfulness of this note

   Adding indexes to existing tables can significantly enhance query performance, especially in large databases. The add_clean_index function simplifies this process by automatically removing an existing index before creating a new one, preventing potential conflicts and duplication.

   Recommendations:

   1. Check Existing Indexes: Before adding an index, ensure that it is necessary and does not already exist in the table. This helps avoid redundant indexes that can slow down insert and update operations.
   2. Index Naming: Use meaningful names for indexes to facilitate their identification and management in the future. For example, instead of a simple post_name, use idx_posts_post_name.
   3. Error Handling: Consider extending the function to handle potential errors during SQL query execution. This will help identify and resolve issues during the development phase.

   function add_clean_index( $table, $index ) {
       global $wpdb;
   
       if ( drop_index( $table, $index ) ) {
           $result = $wpdb->query( $wpdb->prepare( “ALTER TABLE `%s` ADD INDEX (`%s`)”, $table, $index ) );
           if ( false === $result ) {
               // Log the error or notify the administrator
               error_log( “Failed to add index `$index` to table `$table`.” );
               return false;
           }
           return true;
       }
       return false;
   }