WordPress con Integridad Relacional

WordPress por defecto suele venir preparado para una base de datos en MySQL con su sistema de almacenamiento en MyISAM. Pero este sistema no tiene integridad referencial. Esto puede llegar a implicar que la velocidad a la hora de relacionar los campos de tablas distintas no acaba de ser el que debería, y hay una posibilidad de mejorarlo con innoDB.

Con el siguiente script se puede convertir la base de datos a este sistema:

ALTER TABLE cb_commentmeta ENGINE=InnoDB;
ALTER TABLE cb_comments ENGINE=InnoDB;
ALTER TABLE cb_links ENGINE=InnoDB;
ALTER TABLE cb_options ENGINE=InnoDB;
ALTER TABLE cb_postmeta ENGINE=InnoDB;
ALTER TABLE cb_posts ENGINE=InnoDB;
ALTER TABLE cb_terms ENGINE=InnoDB;
ALTER TABLE cb_term_relationships ENGINE=InnoDB;
ALTER TABLE cb_term_taxonomy ENGINE=InnoDB;
ALTER TABLE cb_usermeta ENGINE=InnoDB;
ALTER TABLE cb_users ENGINE=InnoDB;

ALTER TABLE cb_comments ADD INDEX ( user_id ) ;
ALTER TABLE cb_links ADD INDEX ( link_owner ) ;
ALTER TABLE cb_posts ADD INDEX ( guid ) ;
ALTER TABLE cb_posts ADD INDEX ( post_date ) ;
ALTER TABLE cb_term_taxonomy ADD INDEX ( term_id ) ;
ALTER TABLE cb_term_taxonomy ADD INDEX ( parent ) ;
ALTER TABLE cb_users ADD INDEX ( user_email ) ;

UPDATE cb_term_taxonomy SET term_id=NULL WHERE term_id=0;
UPDATE cb_term_relationships SET term_taxonomy_id=NULL WHERE term_taxonomy_id=0;
UPDATE cb_term_taxonomy SET parent=NULL WHERE parent=0;
UPDATE cb_commentmeta SET comment_id=NULL WHERE comment_id=0;
UPDATE cb_links SET link_owner=NULL;
UPDATE cb_postmeta SET post_id=NULL WHERE post_id=0;
UPDATE cb_usermeta SET user_id=NULL WHERE user_id=0;
UPDATE cb_comments SET comment_post_ID=NULL WHERE comment_post_ID=0;
UPDATE cb_comments SET user_id=NULL WHERE user_id=0;
UPDATE cb_comments SET comment_parent=NULL WHERE comment_parent=0;
UPDATE cb_posts SET post_author=NULL WHERE post_author=0;

ALTER TABLE cb_term_taxonomy CHANGE term_id term_id BIGINT( 20 ) UNSIGNED NULL DEFAULT NULL ;
ALTER TABLE cb_term_relationships CHANGE term_taxonomy_id term_taxonomy_id BIGINT( 20 ) UNSIGNED NULL DEFAULT NULL ;
ALTER TABLE cb_term_taxonomy CHANGE parent parent BIGINT( 20 ) UNSIGNED NULL DEFAULT NULL ;
ALTER TABLE cb_commentmeta CHANGE comment_id comment_id BIGINT( 20 ) UNSIGNED NULL DEFAULT NULL ;
ALTER TABLE cb_links CHANGE link_owner link_owner BIGINT( 20 ) UNSIGNED NULL DEFAULT NULL ;
ALTER TABLE cb_postmeta CHANGE post_id post_id BIGINT( 20 ) UNSIGNED NULL DEFAULT NULL ;
ALTER TABLE cb_usermeta CHANGE user_id user_id BIGINT( 20 ) UNSIGNED NULL DEFAULT NULL ;
ALTER TABLE cb_comments CHANGE comment_post_ID comment_post_ID BIGINT( 20 ) UNSIGNED NULL DEFAULT NULL ;
ALTER TABLE cb_comments CHANGE user_id user_id BIGINT( 20 ) UNSIGNED NULL DEFAULT NULL ;
ALTER TABLE cb_comments CHANGE comment_parent comment_parent BIGINT( 20 ) UNSIGNED NULL DEFAULT NULL ;
ALTER TABLE cb_posts CHANGE post_author post_author BIGINT( 20 ) UNSIGNED NULL DEFAULT NULL ;

ALTER TABLE cb_links ADD FOREIGN KEY ( link_owner ) REFERENCES cb_users (ID) ON DELETE RESTRICT ON UPDATE  RESTRICT ;
ALTER TABLE cb_term_taxonomy ADD FOREIGN KEY ( term_id ) REFERENCES cb_terms (term_id) ON DELETE RESTRICT ON UPDATE  RESTRICT ;
ALTER TABLE cb_term_relationships ADD FOREIGN KEY ( term_taxonomy_id ) REFERENCES cb_term_taxonomy (term_taxonomy_id) ON DELETE RESTRICT ON UPDATE  RESTRICT ;
--ALTER TABLE cb_term_taxonomy ADD FOREIGN KEY ( parent ) REFERENCES cb_term_taxonomy (term_taxonomy_id) ON DELETE RESTRICT ON UPDATE  RESTRICT ;
ALTER TABLE cb_usermeta ADD FOREIGN KEY ( user_id ) REFERENCES cb_users (ID) ON DELETE RESTRICT ON UPDATE  RESTRICT ;
ALTER TABLE cb_postmeta ADD FOREIGN KEY ( post_id ) REFERENCES cb_posts (ID) ON DELETE RESTRICT ON UPDATE  RESTRICT ;
ALTER TABLE cb_commentmeta ADD FOREIGN KEY ( comment_id ) REFERENCES cb_comments (comment_ID) ON DELETE RESTRICT ON UPDATE  RESTRICT  ;
ALTER TABLE cb_comments ADD FOREIGN KEY ( comment_post_ID ) REFERENCES cb_posts (ID) ON DELETE RESTRICT ON UPDATE  RESTRICT ;
--ALTER TABLE cb_comments ADD FOREIGN KEY ( comment_parent ) REFERENCES cb_comments (comment_ID) ON DELETE RESTRICT ON UPDATE  RESTRICT ;
--ALTER TABLE cb_comments ADD FOREIGN KEY ( user_id ) REFERENCES cb_users (ID) ON DELETE RESTRICT ON UPDATE  RESTRICT ;

De todas formas, si aplicamos esto, debido a que WordPress no está bien desarrollado, hay dos elementos que no se pueden relacionar, sino el sistema deja de funcionar, que son las relaciones de etiquetas con entradas, y de los comentarios.

Eso significa que las consultas marcadas como comentarios no deben ejecutarse a menos que se haga un pequeño hackeo del código fuente del propio WordPress para eliminar esta incompatibilidad.