Friday, November 18, 2011

Wordpress from a MySQL DBA view

Wordpress is a very popular Content Management System, usually built on top of a MySQL Database. Recently I attended a local Wordpress user group an that started me wondering what the database tables look like. So I installed a new Wordpress installation on a Ubuntu test box running MySQL 5.6.2.

Wordpress creates eleven tables: wp-commentmeta, wp_comments, wp_links, wp_options, wp_postmeta, wp_posts, wp_term_relationships, wp_term_taxonomy, wp_terms, wp_usermeta, and wp_users.

MySQL Workbench generated an EER Model you can see nearby. The first thing that caught me eye was the use of unsigned BIGINTs in every table as keys. Usually you want to use the smallest data type you can for keys. The smaller the key, usually the fast the system can evaluate it. But then maybe there are blog out there that need 18,446,744,073,709,551,615 posts or authors or links.

Hmm, can I start a side business making Wordpress faster by converting all those BIGINTs for those who only to expect 4,294,967,295 posts??

Since I am using MySQl 5.6, the table formats defaulted to InnoDB. Some plugins for Wordpress require full-text search indexes which is now available in the 5.6 preview release.